Search code examples
sqlpatindex

Finding pattern using patindex in SQL

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?

Solution

  • 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.