Tuesday, February 13, 2007

Pattern match for data in column

Sometimes I need to make sure that data in a column does not contain any letters or numbers. I use the following code to match patterns in text...

----- Pattern match

----- PATINDEX returns first position where match happens

SELECT PATINDEX( '%abc%', '123456abc123')
SELECT PATINDEX( '%abc%', ColumnName)

----- To check if the text or column contains any letters
SELECT PATINDEX( '%[A-Z]%', '123456q')

----- To check if the text or column contains any number
SELECT PATINDEX( '%[0-9]%', 'abcde1fg')

No comments: