SQL Server How to test if a string has only digit characters

Question

I am working in SQL Server 2008   I am trying to test whether a string  varchar  has only digit characters  0-9    I know that the IS NUMERIC function can give spurious results    My data can possibly have   signs  which should not pass the test    So  I m avoiding that function   I already have a test to see if a string has any non-digit characters  i e    some column LIKE     0123456789      I would think that the only-digits test would be something similar  but I m drawing a blank   Any ideas

User · Accepted Answer

Use Not Like   where some column NOT LIKE     0-9      Demo  declare  str varchar 50   50 -- asdarew345   select 1 where  str NOT LIKE     0-9

User · Answer

I was attempting to find strings with numbers ONLY  no punctuation or anything else   I finally found an answer that would work here  Using PATINDEX     0-9     some column    0 allowed me to filter out everything but actual number strings

User · Answer

Method that will work  The way it is used above will not work  declare  str varchar 50   79136   select    case      when   str LIKE replicate   0-9   LEN  str   then 1      else 0    end  declare  str2 varchar 50   79D136   select    case      when   str2 LIKE replicate   0-9   LEN  str   then 1      else 0    end

User · Answer

Solution       where some column NOT LIKE     0-9    Is correct    Just one important note  Add validation for when the string column       empty string   This scenario will return that    is a valid number as well

User · Answer

DECLARE  x int 1 declare  exit bit 1 WHILE  x lt  len  123c   AND  exit 1 BEGIN IF ascii SUBSTRING  123c   x 1   BETWEEN 48 AND 57 BEGIN set  x  x 1 END ELSE BEGIN SET  exit 0 PRINT  string is not all numeric  -    END END

User · Answer

The selected answer does not work  declare  str varchar 50   79D136   select 1 where  str NOT LIKE     0-9     I don t have a solution but know of this potential pitfall   The same goes if you substitute the letter  D  for  E  which is scientific notation

User · Answer

There is a system function called ISNUMERIC for SQL 2008 and up  An example   SELECT myCol FROM mTable WHERE ISNUMERIC myCol  lt  gt  1    I did a couple of quick tests and also looked further into the docs   ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type  otherwise it returns 0   Which means it is fairly predictable for example  -9879210433 would pass but 987921-0433 does not   9879210433 would pass but 9879210 433 does not   So using this information you can weed out based on the list of valid currency symbols and    amp  - characters

[sql] SQL Server : How to test if a string has only digit characters

Demo

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008