Check if varchar is a number (TSQL)

Question

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

Examples:

abc123 → no number

123 → yes, its number

Thank:)

Answer 1

ISNUMERIC will be

Also check out the NOTES section in the article.

Answer 2

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

£, $, 0d0

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

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

Answer 3

you can check it like

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

Answer 4

Using SQL Server 2012+, you can use the TRY_ * functions if you have special 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 

Answer 5

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

Answer 6

Wade73 answer for decimal places does not work. I changed it to allow only one decimal point.

 declare @MyTable table(MyVar nvarchar(10)); insert into @MyTable (MyVar) values (N'1234') , (N'000005') , (N'1,000') , (N'293.8457') , (N'x') , (N'+') , (N'293.8457.') , (N'......'); -- This shows that Wade73 answer allows some non-numeric values to slip through. select * from ( select MyVar , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber; -- Notice the addition of "and MyVar not like N'%.%.%'". select * from ( select MyVar , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber; 

Answer 7

Damien_The_Unbeliever noted that he was good only for numbers

Wade73 added decimal point bit

neizan did an additional setup, since he didn’t do this,

Unfortunately, none of them handle negative values, and they seem to have problems with a comma in the value ...

Here is my setting for getting negative values ​​and commas

 declare @MyTable table(MyVar nvarchar(10)); insert into @MyTable (MyVar) values (N'1234') , (N'000005') , (N'1,000') , (N'293.8457') , (N'x') , (N'+') , (N'293.8457.') , (N'......') , (N'.') , (N'-375.4') , (N'-00003') , (N'-2,000') , (N'3-3') , (N'3000-') ; -- This shows that Neizan answer allows "." to slip through. select * from ( select MyVar , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber; -- Notice the addition of "and MyVar not like '.'". select * from ( select MyVar , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' and MyVar not like '.' then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber; --Trying to tweak for negative values and the comma --Modified when comparison select * from ( select MyVar , case when MyVar not like N'%[^0-9.,-]%' and MyVar not like '.' and isnumeric(MyVar) = 1 then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber; 

Answer 8

 DECLARE @A nvarchar(100) = '12' IF(ISNUMERIC(@A) = 1) BEGIN PRINT 'YES NUMERIC' END 

Answer 9

Neizan code allows only "." Values ​​to be used. across. At the risk of becoming too pedantic, I added another AND clause.

 declare @MyTable table(MyVar nvarchar(10)); insert into @MyTable (MyVar) values (N'1234') , (N'000005') , (N'1,000') , (N'293.8457') , (N'x') , (N'+') , (N'293.8457.') , (N'......') , (N'.') ; -- This shows that Neizan answer allows "." to slip through. select * from ( select MyVar , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber; -- Notice the addition of "and MyVar not like '.'". select * from ( select MyVar , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' and MyVar not like '.' then 1 else 0 end as IsNumber from @MyTable ) t order by IsNumber;