check if ssn field in number n ignore alphabets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

check if ssn field in number n ignore alphabets

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-11-26 : 16:25:42

i have a field in my table with SSN
ssn is suppose to be 123-45-5678

but due to some unavailable data people enter
N/A, REQUESTED, NONE, NA, none. stuff like that
so I just want to check where its a number

I did this
AND LEN(LTRIM(RTRIM(REPLACE(SSN,'-','')))) = 9
but i still get 'REQUESTED' and some other alpahbetical fields as one of the results. is there a way to check if that is only numbers and ignore if there are any alphabets in it.

-----------------------------------------------------------------------------------------------
Ashley Rhodes

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-26 : 16:32:57

declare @a table (ssn varchar(20))
insert into @a
select '123-45-6789'
union all select 'REQUESTED'
union all select 'N/A'
union all select 'NONE'

select * from @a

select * from @a where replace(ssn,'-','') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-26 : 16:41:26

[code]
select *
from MyTable
where
SSN like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources