Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

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
0
razza_b
Asked:
razza_b
  • 10
  • 4
  • 2
1 Solution
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 10
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now