Check if a string contains only number

I am trying to check if a string contains only valid number in the following format

123.456
123
.356

But it should reject anything that contains non-numbers including double dots. Here are some invalid formats

d123.456
123d
12d3
d.1256
12d.456
12.d12
12.d45d
12.45.56

I have done the following

SELECT CASE WHEN '123.00' NOT LIKE '%[^0-9.]%' THEN 'Valid' ELSE 'Invalid' END

When seems to work except for the case where there is more than one dot in the string.

How can I tweak the regular expression to only allow one dot otherwise return ‘Invalid’?

Answer

I would suggest try_convert():

select (case when try_convert(col, float) is not null then 'valid' else 'invalid' end)

The one possible downside is exponential format; 1e6 is a valid number for instance.

An alternative is the where approach; you just need more complete logic:

select (case when col like '%[^0-9.]%' then 'invalid'
             when col like '%.%.%' then 'invalid'
             else 'valid'
        end)