How to select only numeric values

Question

Table1

id

01
wire
02
steve
ram123
03
....

from the table1 i want to select only numeric values, It should not display alphanumeric values like (ram123)

Expected Output

01
02
03
....

How to make a query for this condition

Solution 1

Try ISNUMERIC

SELECT *
FROM Table1
WHERE ISNUMERIC([ID]) = 1

SQLFiddle Demo

Solution 2

SELECT * FROM @Table 
WHERE Col NOT LIKE '%[^0-9]%' 

Solution 3

Just want to note that IsNumeric() has some limitations. For example all of the below will return 1.

SELECT ISNUMERIC(' - ')
SELECT ISNUMERIC(' , ')
SELECT ISNUMERIC('$')
SELECT ISNUMERIC('10.5e-1')
SELECT ISNUMERIC('$12.09')

So if you only looking to select numbers ONLY, then something like this could work:

create function [dbo].[IsNumbersOnly](@strSrc as varchar(255))
returns tinyint
as
begin

    return isnumeric(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
        @strSrc, '\', 'x'), '-', 'x'), ',', 'x'), '+', 'x'), '$', 'x'), '.', 'x'), 'e', 'x'), 'E', 'x'),
        char(9), 'x'), char(0), 'x'))
end

Comments

[Mahmoud Gamal]

How is that answer different from the other answer?

[GilM]

Or just use WHERE ID NOT LIKE '%[^0-9]%'

[Void Ray]

That would work too; just wanted to mention the limitations... Thanks!

[highwingers]

SELECT * FROM @Table WHERE Col NOT LIKE '%[^0-9.-]%'

[GilM]

This shows that it isn't clear what "Numeric" means. From the sample data in the original question, it looks like it might be purely numeric digits (in which case this original answer is correct), or it might include other characters like "-" and ".". Maybe it means what Microsoft has implemented with ISNUMERIC, but I've never heard of anyone expecting it to meant that :-) .

[highwingers]

yes GilM, I never relied on isNumeric function in SQL or Asp Classic.

[jatin Goyal]

This Query Works in Oracle