asked on

Find leading spaces in column

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

ASKER CERTIFIED SOLUTION

duttcom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edtechdba

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

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

+ 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)

awking00

To find records beginning with a space -
WHERE CHARINDEX(' ',SSG_Acquisition_All_T.Serial_Number) = 1

Fred Webb

ASKER

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.

Fred Webb

ASKER

As always EE experts have come to the rescue again

PortletPaul

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