sql – Check if a string contains only number

sql – Check if a string contains only number

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)

Theres a sql server built in function:

Select CASE WHEN isnumeric([fieldname]) THEN Valid ELSE Invalid END

sql – Check if a string contains only number

TRY_PARSE will let you compare the input value to any/all numeric datatypes you decide to allow — for example:

SELECT 
   TRY_PARSE(123.456 as int) as [int],
   TRY_PARSE(123.0 as float) as [float],
   TRY_PARSE(d123.456 as int) as [int],
   TRY_PARSE(d123.456 as float) as [float]

FWIW — ISNUMERIC is often suggested, and is certainly the best-sounding function name 🙂 — but doesnt work the way most folks seem to expect. (It allows math and currency symbols, etc.)

Leave a Reply

Your email address will not be published. Required fields are marked *