How to get only numeric column values

Question

Using SQL Server 2005  I want to get only numeric values from the table  Column1  12345 asdf 2312 ase acd        Tried Query  Select Isnumeric column1  from table   Showing Result as  1 0 1 0 0       I need the colum1 numeric value  Need SQL Server Query help

User · Answer

Try using the WHERE clause   SELECT column1 FROM table WHERE Isnumeric column1

User · Answer

The other answers indicating using IsNumeric in the where clause are correct  as far as they go  but it s important to remember that it returns 1 if the value can be converted to any numeric type  As such  oddities such as  1d3  will make it through the filter   If you need only values composed of digits  search for that explicitly   SELECT column1 FROM table WHERE column1 not like     0-9      The above is filtering to reject any column which contains a non-digit character  Note that in any case  you re going to incur a table scan  indexes are useless for this sort of query

User · Answer

Use This  Tested  To get numeric SELECT column1 FROM table WHERE Isnumeric column1    1     will return Numeric values  To get non-numeric SELECT column1 FROM table WHERE Isnumeric column1    0     will return non-numeric values

User · Answer

SELECT column1 FROM table WHERE ISNUMERIC column1    1   Note  as Damien The Unbeliever has pointed out  this will include any valid numeric type   To filter out columns containing non-digit characters  and empty strings   you could use  SELECT column1 FROM table WHERE column1 not like     0-9    and column1

User · Answer

SELECT column1 FROM table WHERE column1 not like    0-9      Removing the     did it for me  I m looking at a varchar field and when I included the   it excluded all of my non-numerics which is exactly what I didn t want  So  by removing   I only got non-numeric values back

[sql-server] How to get only numeric column values?

Examples related to sql-server