sql like operator to get the numbers only

Question

This is I think a simple problem but not getting the solution yet  I would like to get the valid numbers only from a column as explained here   Lets say we have a varchar column with following values  ABC Italy Apple 234 62 2 234 43 22 France 6435 23 2 Lions   Here the problem is to select numbers only   select   from tbl where answer like    0-9     would have done it but it returns       234 62     2 234 43 22     6435 23     2   Here  obviously  2 234 43 22 is not desired as it is not valid number   The desired result is          234 62         6435 23         2   Is there a way to do this

User · Accepted Answer

You can try this  ISNUMERIC  Transact-SQL      ISNUMERIC returns 1 when the input   expression evaluates to a valid   numeric data type  otherwise it   returns 0    DECLARE  Table TABLE          Col VARCHAR 50     INSERT INTO  Table SELECT  ABC   INSERT INTO  Table SELECT  Italy   INSERT INTO  Table SELECT  Apple   INSERT INTO  Table SELECT  234 62   INSERT INTO  Table SELECT  2 234 43 22   INSERT INTO  Table SELECT  France   INSERT INTO  Table SELECT  6435 23  INSERT INTO  Table SELECT  2   INSERT INTO  Table SELECT  Lions   SELECT    FROM     Table WHERE   ISNUMERIC Col    1

User · Answer

what might get you where you want in plain SQL92   select   from tbl where lower answer    upper answer    or  if you also want to be robust for leading trailing spaces   select   from tbl where lower answer    trim upper answer

User · Answer

Try something like this - it works for the cases you have mentioned   select   from tbl where answer like    0-9    and answer not like         and answer not like    A-Z

User · Answer

With SQL 2012 and later  you could use TRY CAST TRY CONVERT to try converting to a numeric type  e g  TRY CAST answer AS float  IS NOT NULL -- note though that this will match scientific notation too  1 E34     If you use decimal  then scientific notation won t match

User · Answer

You can use the following to only include valid characters   SQL  SELECT   FROM  Table WHERE Col NOT LIKE     0-9       Results  Col --------- 234 62 6435 23 2

[sql] sql like operator to get the numbers only

Examples related to sql

Examples related to sql-server

Examples related to numbers