Link to home
Start Free TrialLog in
Avatar of razza_b
razza_b

asked on

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
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Please explain "right of the column" and "left of that column".  What is the column you are referring to?
Avatar of razza_b
razza_b

ASKER

just needs to look like this...attached
SMT2-output.docx
Avatar of razza_b

ASKER

the column is the JobDInstruction, all the data on the left i want removed so its like my second attachent....
Avatar of razza_b

ASKER

sorry on the right removed!!
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.
Avatar of razza_b

ASKER

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...
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.
Avatar of razza_b

ASKER

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...
Avatar of razza_b

ASKER

is this ok?...

SMT2-output.docx
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?)
Avatar of razza_b

ASKER

should also mention that the desired output on the right doesnt need to show the [SMT2] just the instructions that is shown from SAP...
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of razza_b

ASKER

PERFECT THANKS VERY MUCH