how to remove certain rows from query from columns that has data to the right of the column

Hi

I have a simple query that does this (see attached for output of query)...

                                         SELECT JobdJobKey,JobdStationIDKey,JobdInstruction,JobdRecIdKey
                                         FROM TblJobDetail
                                         WHERE JobdJobKey = 'PNOE143303'  AND JobdStationIDKey='SMT2'
                                                AND JobDInstruction IS NOT NULL
                                                AND JobDInstruction <> ''

and what i need  is to remove the data that is over to the right of the column and just keep all the data on the left of that column.

Hope that makes sense.

Thanks
query-output-from-snippet.docx
LVL 1
razza_bAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Please explain "right of the column" and "left of that column".  What is the column you are referring to?
0
razza_bAuthor Commented:
just needs to look like this...attached
SMT2-output.docx
0
razza_bAuthor Commented:
the column is the JobDInstruction, all the data on the left i want removed so its like my second attachent....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

razza_bAuthor Commented:
sorry on the right removed!!
0
Brian CroweDatabase AdministratorCommented:
On the left of what?  I'm guessing you are not a SQL Server user because your terminology is all over the place.  SQL Server is a data store not a report engine.  If you don't like the way it is displayed then that's a formatting issue not a SQL Server issue.

I don't see any difference between the two documents from a data perspective other than spacing.

If you don't want the JobdJobKey column in your output then don't include it in your SELECT clause.
0
razza_bAuthor Commented:
To remove the data on the right hand side of the column (JobDInstruction)

the difference between the 2 docs is that one doesnt have all the data to the right of the column and one does.

All i want to know is there a way to try and remove the data of the column on the right hand side so it look like the second attached doc...
0
Brian CroweDatabase AdministratorCommented:
Do you mean values in the JobdInstruction column that have white space on the left like "Total Reject Modules at SMT = ______________" and may or may not have additional text at the beginning of the string?  If that is the case then you are essentially looking to strip off the string following and including any protracted white space.

First you're going to need to determine through testing what that white space consists of is it spaces or tabs?

SELECT JobdInstruction, CHARINDEX('     ' /*five spaces*/, JobdInstruction, 1) AS StartOfWhiteSpace
FROM TblJobDetail 
WHERE JobdJobKey = 'PNOE143303'  AND JobdStationIDKey='SMT2'
   AND JobDInstruction IS NOT NULL 
   AND JobDInstruction <> ''

Open in new window


If a value greater than 0 is returned for the column values which need to be trimmed then we can move on to the next step.  Let me know.
0
razza_bAuthor Commented:
yes thats exactly the case, so all the data on the right in that column in row 1,2,4,6,7 and 9 to get removed.

It kind of looks like TAB

Thanks...
0
razza_bAuthor Commented:
is this ok?...

SMT2-output.docx
0
PortletPaulfreelancerCommented:
any chance of outputting a sample of source data to a .csv file? (or any file format that will preserve the white space)

(maybe a copy and paste {of unformatted text} into a text editor?)
0
razza_bAuthor Commented:
0
razza_bAuthor Commented:
should also mention that the desired output on the right doesnt need to show the [SMT2] just the instructions that is shown from SAP...
0
razza_bAuthor Commented:
0
PortletPaulfreelancerCommented:
Is this close?
SELECT
      JobdJobKey
    , JobdStationIDKey
    , RTRIM(SUBSTRING(JobdInstruction, 1, 74)) AS JobdInstruction
    , JobdRecIdKey
FROM TblJobDetail
WHERE JobdJobKey = 'PNOE143303'
      AND JobdStationIDKey = 'SMT2'
      AND JobDInstruction IS NOT NULL
      AND RTRIM(SUBSTRING(JobdInstruction, 1, 74)) <> ''
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
Razza,

Did you run the script I provided earlier to see if we are targeting the correct records?  We need to know what your white space consists of before trying to clean it up.
0
razza_bAuthor Commented:
PERFECT THANKS VERY MUCH
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.