Need a Function to Return Partial String Data 3

[txdave35]

Hey guys,

I have table with a field for SSN stored as text. Ex. 222-22-2222.

I want to run a query that will return only the last 4 digits or characters of the SSN. Is there a SQL function to do this? I've played around with RIGHT(), but doesn't seem to work for me. Thanks.

 

[gmmastros]

I've played around with RIGHT(), but doesn't seem to work for me.

Can you show how you are using it?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bigfoot]

Given the field name is SSN, try this:

select ssn, right(ssn,4) as myright from mytable

this should give you the whole field and the last 4:

ssn myright
------------- -----------------
222-22-2222 2222

hope this helps

 

[markros]

What is the type of the SSN field? Are all data in this field in the correct format?

 

[gmmastros]

In situations like this, it is probably your data this is causing problems. It could be that you have a char column (which is padded with spaces on the right), or you could have trailing non-printable characters, like tab, carriage return, line feed, etc....

I wrote a blog recently that shows how you can extract data from a string.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-extracting-data-again

In the blog (which uses phone numbers as examples), it shows how to remove non numeric values from the data. If you use this function in conjunction with the right function, you should get exactly the data you are looking for.

[tt][blue]
Select Right(dbo.RemoveNonNumericCharacters(SSN),4) as SSN_Right_Four
From YourTable
[/blue][/tt]

By the way, I suspect that your original problem was due to data type. If you use the CHAR data type, the data is padded (on the right) with spaces. So, taking the right 4 characters could return just spaces (where you expect actual data). Ex:

Declare @Test Table(Data [!]Char[/!](100))

Insert Into @Test Values('(111) 222-3333')
Insert Into @Test Values('111-222-3333')
Insert Into @Test Values('111.222.3333')
Insert Into @Test Values('(800) XXX-3333')
Insert Into @Test Values('')
Insert Into @Test Values(NULL)

Select Data,
       Right(Data, 4) As Right4Characters,
       Right(RTrim(Data), 4) As Right4TrimmedCharacters
From   @Test

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I suspected char field as well.

 

[txdave35]

Ahhh, you guys were absolutely right. It is CHAR data type and padded with spaces on the right. I didn't think about that causing a problem.

George's function works great now. Thanks so much!

 

[txdave35]

Can someone show me how to do the same thing if the data type is int? Looks like I have to convert the int to string first. I tried the following, but doesn't work:

RIGHT((CONVERT(A.MBR_SSN_NBR AS char(8)), 4)

 

[bborissov]

RIGHT((CONVERT(A.MBR_SSN_NBR  AS [b][COLOR=red]VAR[/color][/b]char(8)), 4)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[txdave35]

Doesn't work

 

[gmmastros]

if the data type is int, and you want the last 4 characters.... I would suggest that you keep it an int and use the mod operator.

Example:

declare @SSN Table(SSN int)

Insert Into @SSN Values(123456789)
Insert Into @SSN Values(111111111)
Insert Into @SSN Values(000000000)
Insert Into @SSN Values(NULL)

Select ssn, Right('0000' + Convert(VarChar(4), ssn % 10000), 4) From @SSN

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom