Using SELECT to find a social security number in a string 2

[alanmusician]

I'm working with a database that has a character field for notes for the record, and I need to find a way to select all the records that have nine digits in a row inside the character field.

select * from entry where desc like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

doesn't work.

Even if someone could give me a method to find the first numeric character in the string it would help. Thanks,

--Alan

 

[TheRambler]

Hi,

Try something like this:

SELECT * ;
FROM entry ;
WHERE LEN(CHRTRAN(desc,"0123456789",""))<LEN(desc)-8

 

[severach]

While CHRTing the characters away and checking the length isn't the best way to do it, it did remind me how I do these things.

Dashed SSN:
WHERE "000-00-0000"$CHRTRAN(desc,"0123456789","0000000000")

SSN surrounded by whitespace
WHERE " 000000000 "$" "+CHRTRAN(desc,"0123456789"+chr(13)+chr(10)+chr(9),"0000000000 ")+" "

This works for phone numbers, birth dates, zip codes, and many other somewhat formatted numeric things. You can use letters to find lots of other things too. Birthdates may have / or - so you can CHRT them to a common character.

 

[severach]

I read more and noticed you asked for the position of the first numeric character. The same CHRTs will do it.

?POS("0",CHRT(desc,"0123456789","0000000000"))

 

[alanmusician]

Fantastic. That's what I needed, thanks.

 

[TheRambler]

Great! for a while I thought this couldn't be done without those SQL Server wildcard characters, now I see we don't need them yet. It is so easy when you know how.