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,JobdStationIDKe y,JobdInst ruction,Jo bdRecIdKey
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
I have a simple query that does this (see attached for output of query)...
SELECT JobdJobKey,JobdStationIDKe
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
Please explain "right of the column" and "left of that column". What is the column you are referring to?
ASKER
just needs to look like this...attached
SMT2-output.docx
SMT2-output.docx
ASKER
the column is the JobDInstruction, all the data on the left i want removed so its like my second attachent....
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.
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.
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...
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?
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.
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 <> ''
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.
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...
It kind of looks like TAB
Thanks...
ASKER
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?)
(maybe a copy and paste {of unformatted text} into a text editor?)
ASKER
csv data...
SMT-2-unformatted-data.xlsx
SMT-2-unformatted-data.xlsx
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
PERFECT THANKS VERY MUCH