Check if a varchar is a number (TSQL)

is there an easy way to figure out if a varchar is a number?

Examples:

abc123 --> no number

123 --> yes, its a number

Thanks :)

Solution 1:

ISNUMERIC will not do - it tells you that the string can be converted to any of the numeric types, which is almost always a pointless piece of information to know. For example, all of the following are numeric, according to ISNUMERIC:

£, $, 0d0

If you want to check for digits and only digits, a negative LIKE expression is what you want:

not Value like '%[^0-9]%'

Solution 2:

ISNUMERIC will do

Check the NOTES section too in the article.

Solution 3:

you can check like this

declare @vchar varchar(50)
set @vchar ='34343';
select case when @vchar not like '%[^0-9]%' then 'Number' else 'Not a Number' end