Finding pattern using patindex in SQL

Question

We have zipcode column that has bad data entered by data entry person. Example: If someone doesn't provide a zipcode, data entry person usually enters 00000 or 0000000 (or 00, or 000 or 0).

I need to design a query that will look into Zipcode column and exclude anythng that has only number 0. So exclude '0', '00', '000', etc. Is it possible with PatIndex?

Is your _RDBMS_ _sql server_ ? patindex returns position of the pattern.. any specific reason for using only patindex on your question? — vhadalgi, Jun 13 '14 at 17:20
It is sql server. There is no specific reason. I just thought patindex could do this trick. — NonProgrammer, Jun 13 '14 at 17:56

score 0 · Answer 1 · answered Jun 13 '14 at 17:06

0

Have you tried using something like ilike? ilike is used as a wildcard to pattern match the zip code you are looking for. EX:

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME ILIKE '%<ENTER VALUE>%';

answered Jun 13 '14 at 17:06

score 0 · Accepted Answer · answered Jun 13 '14 at 17:13

You should be able to use like if your database version allows for regular expressions.

WHERE ZIPCODE NOT LIKE '%[^0]%'

The ^ means not zero. So the regular expressions says match anything that is not zero and then that is negated by the NOT. So we find anything that is all zeros.

Finding pattern using patindex in SQL

2 Answers2