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.
This should be pretty simple:
select (case when try_convert(float, col) = 0 then 1 else 0 end) as IsZero
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