Solved

# I need help removing a leading 0 in my query

Posted on 2013-10-10
Medium Priority
380 Views
Hi Experts,
I need to create a query that will return a list of disctint Social Security Numbers from my Students table.  The problem I have is that some of the Social Security Numbers in my SSN column have a leading 0.  My query needs to check for any Social Security Numbers with a length > 9 and removed the leading 0. How can I do this?

mrotor
0
Question by:mainrotor
• 3

LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 39563237
SELECT RIGHT(SSN, 9) as ssn from yourTable
0

LVL 32

Expert Comment

ID: 39563241
select case when len(ssn) > 9 then substring(ssn,2,9) else ssn end
0

LVL 66

Expert Comment

ID: 39563251
>The problem I have is that some of the Social Security Numbers in my SSN column have a leading 0.
For starters, this implies that the SSN column is a char field of some sort, and not numeric, as numeric columns do not support leading zeros.

>check for any Social Security Numbers with a length > 9 and removed the leading 0. How can I do this?

SELECT SSN as ssn_greater_than_nine_characters
FROM YourTable
WHERE LEN(CAST(CAST(SSN as bigint) as varchar(25)) ) > 9
0

LVL 66

Expert Comment

ID: 39563259
For example..
``````CREATE TABLE #ssn (ssn varchar(25))

INSERT INTO #ssn (ssn) VALUES('123456789'), ('012345678'), ('00000000001'), ('12345678912')

SELECT SSN as ssn_greater_than_nine_characters
FROM #ssn
WHERE LEN(CAST(CAST(SSN as bigint) as varchar(25)) ) > 9
``````
0

LVL 66

Expert Comment

ID: 39563282
Just curious ... how does the solution that was accepted handle leading zeros?
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.