user2633379 user2633379 - 10 months ago 56
SQL Question

SQL Server - How to check if string is text or any representation of 0?

I have a 'non-clean' column Col1 of type nvarchar(20) containing numbers (both integers and decimals) and various representations of the text 'No Data' (e.g no-data, no data, NO DATA, etc) for cases where the value cannot be found.

Am looking for a query which will fetch me only records that contain ANY representation of 0 (like 0, 00, 0.00, etc) or the exact text 'NO-DATA'

I am unable to find ways to use regular expression in SQL Server 2016.

Answer Source

This should be pretty simple:

select (case when try_convert(float, col) = 0 then 1 else 0 end) as IsZero

try_convert() returns NULL if the conversion doesn't work.

In general, comparison of floating point numbers to constants isn't recommended. In this case, I'm pretty sure all reasonable representations of zero would be exact.

If that is a concern, you could do:

select (case when try_convert(int, replace(col, '.', '')) = 0 then 1 else 0 end) as IsZero

Of course, this would allow 0.0.0.