Check if a varchar is a number (TSQL)

Question

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 do

Check the NOTES section too in the article.

Solution 2

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 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

Solution 4

Using SQL Server 2012+, you can use the TRY_* functions if you have specific needs. For example,

-- will fail for decimal values, but allow negative values
TRY_CAST(@value AS INT) IS NOT NULL 

-- will fail for non-positive integers; can be used with other examples below as well, or reversed if only negative desired
TRY_CAST(@value AS INT) > 0

-- will fail if a $ is used, but allow decimals to the specified precision
TRY_CAST(@value AS DECIMAL(10,2)) IS NOT NULL 

-- will allow valid currency
TRY_CAST(@value AS MONEY) IS NOT NULL  

-- will allow scientific notation to be used like 1.7E+3
TRY_CAST(@value AS FLOAT) IS NOT NULL 

Solution 5

I ran into the need to allow decimal values, so I used not Value like '%[^0-9.]%'

View more solutions

Comments

[Sachin Shanbhag]

@Grady - I think Damien's answer below is a better one as pointed out, IsNumeric does have some restrictions. Are you sure, you dont encounter any such restrictions?

[Ewald Stieger]

ISNUMERIC also considers a "-" to be numeric.

[greg121]

scroll down to notice the next answer.

[marknuzz]

"select isnumeric('138D47')" returns 1

[Jonathan Stark]

And I had to add <> '.' Thankfully none of my data has a '-' in the wrong place

[Dan Field]

This is a great method if you only want positive integers, but I've added some examples below if you need to allow other kinds of numeric types too if you're using SQL 2012+.

[iaforek]

Definitely, better answer than ISNUMERIC! This should be the marked as correct answer.

[RosieC]

This and other similar answers will say the first example in the original question IS a number, because one of it's characters is a number. What about if you want to know that ALL characters are numbers.

[RosieC]

This is the best solution. The solutions using %[^0-9.]% seem to return say it's a number when it contains a number even if there are also characters in the string. This TRY_CAST works for me.

[Damien_The_Unbeliever]

@RosieC - no, the example in my code, not Value like '%[^0-9]%', is, exactly, that all characters are digits. It's a double-negative. It asserts that no character in Value is outside of the range 0-9.

[Dan Field]

Just keep in mind it requires SQL Server 2012+ - won't work on 2008R2 or lower, and won't work in other RDBMS

[Damien_The_Unbeliever]

@RosieC - or, to put it another way - Value like '%[0-9]%' would be "the string contains at least one digit character". Value like '%[^0-9]%' would be "the string contains at least one non-digit character". But, again, my test is not Value like '%[^0-9]%' which negates the previous test and is "the string contains no non-digit characters"

[Andrew Hill]

-1 There are many things which isNumeric is true for, but which will exception if cast to INT or Float -- see brentozar.com/archive/2018/02/fifteen-things-hate-isnumeric for some examples - which shows the following 15 strings are all "IsNumeric"=1 '$' ,'£' ,',' ,'.' ,'0e+99' ,'2e2' ,'12D4' ,',1,1,1,1,1,1,1' ,'-' ,'+' ,CHAR(9) ,CHAR(10) ,CHAR(11) ,CHAR(12) ,CHAR(13)

[Valentin C]

@Nuzzolilo Because it seems to be numeric if it is an hexadeciaml thing, and d is part of hexa thing

[Ketan Vaghasiya]

I am still getting error on below query Declare @testVal as varchar(50)='ok' select CASE WHEN IsNUMERIC(@testVal)=1 THEN Cast(@testVal as numeric) ELSE 'n' End Error converting data type varchar to numeric.

[The_Rafi]

This is not good. It would have matched with just ., 2.2.2...1....0, 2.1.2 and so on. You would need a second condition to also exclude ones with more than one decimal point, and those where the decimal is not between two numbers.