CODEWITHSUNDEEP
sqlsql-serverregexsql-like
user3481644
user3481644

Reputation: 440

Using SQL - how do I match an exact number of characters?

My task is to validate existing data in an MSSQL database. I've got some SQL experience, but not enough, apparently. We have a zip code field that must be either 5 or 9 digits (US zip). What we are finding in the zip field are embedded spaces and other oddities that will be prevented in the future. I've searched enough to find the references for LIKE that leave me with this "novice approach":

ZIP NOT LIKE '[0-9][0-9][0-9][0-9][0-9]'
AND ZIP NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Is this really what I must code? Is there nothing similar to...?

ZIP NOT LIKE '[\d]{5}' AND ZIP NOT LIKE '[\d]{9}'

I will loath validating longer fields! I suppose, ultimately, both code sequences will be equally efficient (or should be).

Thanks for your help

Upvotes: 1

Views: 9486

Answers (4)

ddmps
ddmps

Reputation: 4380

Unfortunately, LIKE is not regex-compatible so nothing of the sort \d. Although, combining a length function with a numeric function may provide an acceptable result:

WHERE ISNUMERIC(ZIP) <> 1 OR LEN(ZIP) NOT IN(5,9)

I would however not recommend it because it ISNUMERIC will return 1 for a +, - or valid currency symbol. Especially the minus sign may be prevalent in the data set, so I'd still favor your "novice" approach.

Another approach is to use:

ZIP NOT LIKE '%[^0-9]%' OR LEN(ZIP) NOT IN(5,9)

which will find any row where zip does not contain any character that is not 0-9 (i.e only 0-9 allowed) where the length is not 5 or 9.

Upvotes: 3

Hart CO
Hart CO

Reputation: 34774

To test for non-numeric values you can use ISNUMERIC():

WHERE ISNUMERIC(ZIP) <> 1

Upvotes: 0

Amrit
Amrit

Reputation: 421

You are looking for LENGTH()

select * from table WHERE length(ZIP)=5;

select * from table WHERE length(ZIP)=9;

Upvotes: 0

glaeran
glaeran

Reputation: 426

There are few ways you could achieve that.

  1. You can replace [0-9] with _ like

    ZIP NOT LIKE '_'

  2. USE LEN() so it's like

    LEN(ZIP) NOT IN(5,9)

Upvotes: 0

Related Questions