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

x
?
Solved

Find leading spaces in column

Posted on 2013-06-30
8
Medium Priority
?
312 Views
Last Modified: 2013-07-01
I have a problem, I am trying to find all serial number in my query that have a space as the first character. I have tried the script below but because of padding there are trailing spaces in the serial result  and is returning all serials not just the ones with leading spaces. I am not  concerned about the trailing spacing, just the leading spaces. I even tried RTRIM in the statement same result all rows returned.

SELECT     SSG_Acquisition_All_T.Type, SSG_Acquisition_All_T.Received_From, SSG_Acquisition_All_T.MANUFACTURE, SSG_Acquisition_All_T.Firearm_Model, 
                      SSG_Acquisition_All_T.Firearm_Caliber, SSG_Acquisition_All_T.Item_Number, SSG_Acquisition_All_T.Serial_Number, 
                      SSG_Dispostion_All_T.Serial_Number AS DISP_SN, SSG_Dispostion_All_T.ItemNo AS DIS_ITEM, SSG_Dispostion_All_T.Ship_To_Name
FROM         SSG_Acquisition_All_T RIGHT OUTER JOIN
                      SSG_Dispostion_All_T ON SSG_Acquisition_All_T.Serial_Number = SSG_Dispostion_All_T.Serial_Number
WHERE     (SSG_Acquisition_All_T.Serial_Number IS NULL) AND (CHARINDEX(' ', SSG_Dispostion_All_T.Serial_Number)) >0
ORDER BY DISP_SN

Open in new window


WHERE     (SSG_Acquisition_All_T.Serial_Number IS NULL) AND RTRIM(CHARINDEX(' ', SSG_Dispostion_All_T.Serial_Number)) >0

Open in new window

0
Comment
Question by:skull52
8 Comments
 
LVL 12

Accepted Solution

by:
duttcom earned 1600 total points
ID: 39288720
I'm not sure why you have serial number is null included in your where statement. If you were trying to select only serial numbers which start with a space, you could use -

WHERE     LEFT(SSG_Acquisition_All_T.Serial_Number,1) =' '

Open in new window

0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39288724
How about using the substring function to isolate the 1st character in the serial # string?

WHERE SUBSTRING(SSG_Acquisition_All_T.Serial_Number,1,1) = ' '

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 39288743
seems you are looking for just those disposition records which:
1. don't have a matching acquisition record
2. have a leading space in Serial_Number

for 1. I would swap the table precedence to use a left join to locate the non-matched disposition records.
for 2. Use the left function on: SSG_Dispostion_All_T.Serial_Number
(substring could be used as an alternative) my point here really is the correct table

please try this
SELECT
       SSG_Acquisition_All_T.Type
     , SSG_Acquisition_All_T.Received_From
     , SSG_Acquisition_All_T.MANUFACTURE
     , SSG_Acquisition_All_T.Firearm_Model
     , SSG_Acquisition_All_T.Firearm_Caliber
     , SSG_Acquisition_All_T.Item_Number
     , SSG_Acquisition_All_T.Serial_Number
     , SSG_Dispostion_All_T.Serial_Number AS DISP_SN
     , SSG_Dispostion_All_T.ItemNo AS DIS_ITEM
     , SSG_Dispostion_All_T.Ship_To_Name
FROM SSG_Dispostion_All_T

LEFT JOIN SSG_Acquisition_All_T
       ON SSG_Dispostion_All_T.Serial_Number = SSG_Acquisition_All_T.Serial_Number

WHERE (SSG_Acquisition_All_T.Serial_Number IS NULL)
AND LEFT(SSG_Dispostion_All_T.Serial_Number,1) =' '
     
ORDER BY DISP_SN

Open in new window

{aopologies, 2 edits, bad typos}
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39288759
+ note,
as you are locating just non-matching records via line 17
lines 2 through 8 will all be null as a consequence (and thus may be redundant)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39289978
To find records beginning with a space -
WHERE CHARINDEX(' ',SSG_Acquisition_All_T.Serial_Number) = 1
0
 

Author Comment

by:skull52
ID: 39290260
Paul,
Thanks I took your advice on the LEFT OUTER Join and and removing the redundant columns.   and Duttcom the reason I was using (SSG_Acquisition_All_T.Serial_Number IS NULL) is I only wanted results where there were Dispositions with no Acquisitions and where the serial number started with a space and because you were the first to suggest using LEFT I am awarding you the majority of the points and Paul a few for the suggestion of using the Left OUTER. The  (WHERE CHARINDEX(' ',SSG_Acquisition_All_T.Serial_Number) = 1) and (WHERE SUBSTRING(SSG_Acquisition_All_T.Serial_Number,1,1) = ' ') suggestions both returned 0 results.
0
 

Author Closing Comment

by:skull52
ID: 39290266
As always EE  experts have come to the rescue again
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39292055
Cheers, thanks for the points and quick closure. Paul.

btw :) all 3 methods suggested to locate the leading space will work, they are functionally the same. try this
declare @Serial_Number as varchar(20)
set @Serial_Number = ' leadingSpace'

select
  case when left(@Serial_Number,1) = ' '        then 'found by left'      else 'not found' end as methodleft
, case when substring(@Serial_Number,1,1) = ' ' then 'found by substring' else 'not found' end as methodsubstring
, case when charindex(' ',@Serial_Number) = 1   then 'found by charindex' else 'not found' end as methodcharindex

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question