Search code examples
sqlsql-servervarchar

SQL server, check if varchar contains all zeros

I need to write a function to check if a varchar variable value is all zeros.

As the variable is a varchar and not an int I am a bit lost. What would you do?

Thanks a lot

Solution

  • You can use a double-negative with a LIKE test:

    @variable NOT LIKE '%[^0]%'

    Which says the variable isn't composed of some number of characters, then a character that isn't a 0, followed by some number of characters. The only strings that fail to match that LIKE expression are strings that only contain 0s, and so we use the NOT to invert the result.

    (This does also accept an empty string - whether you consider an empty string to be composed of only 0s can be quite an interesting discussion - it certainly doesn't contain any other characters. A simple LEN(@Variable) test can be used if you do choose to reject this)