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 -- gt  no number 123 -- gt  yes  its a number  Thanks

User · Answer

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

User · Answer

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

User · Answer

Do not forget to exclude carriage returns from your data      as in   SELECT    Myotherval     CASE WHEN TRIM REPLACE  MyVal   char 13    char 10        not like     0-9    and RTRIM REPLACE  MyVal   char 13    char 10        not like     and isnumeric REPLACE  MyVal   char 13    char 10          1 THEN  my number        MyVal               ELSE ISNULL Cast  MyVal  AS VARCHAR 8000                END AS  MyVal  FROM MyTable

User · Answer

Damien The Unbeliever noted that his was only good for digits  Wade73 added a bit to handle decimal points  neizan made an additional tweak as did notwhereuareat  Unfortunately  none appear to handle negative values and they appear to have issues with a comma in the value     Here s my tweak to pick up negative values and those with 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 s 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

User · Answer

Wade73 s answer for decimals doesn t quite work  I ve modified it to allow only a single 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 s 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

User · Answer

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

User · Answer

I ran into the need to allow decimal values  so I used not Value like     0-9

User · Answer

DECLARE  A nvarchar 100     12  IF ISNUMERIC  A    1  BEGIN     PRINT  YES NUMERIC  END

User · Answer

ISNUMERIC will do   Check the NOTES section too in the article

User · Answer

Neizan s code lets values of just a     through  At the risk of getting too pedantic  I added one more 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 s 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

[tsql] Check if a varchar is a number (TSQL)

Examples related to tsql

Examples related to numerical-methods