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

Prognosis

Answer 1

Try ISNUMERIC

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

SQLFiddle Demo

Irreducible

Answer 2

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

Sproul

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

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 :-) . – Norway

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

Answer 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

Kiker

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

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

Answer 4

I tried the code above (ISNUMERIC()) and it somehow doesn't work in Oracle SQL.
But I found a working solution for Oracle SQL:

SELECT column1
FROM Table
WHERE regexp_like( column1, '^[[:digit:]]*$');

From: https://community.oracle.com/thread/2394572

Enroll

Answer 5

 SELECT column1 FROM table where ISNUMERIC(column1) = 1

Foreside

How is that answer different from the other answer? – Hawken

This Query Works in Oracle – Dkl

Answer 6

You can use translate and replace function together. first translate the numbers to 0 then replace them with null and return only null values can solve the problem.

select column from table where  replace(translate(column,'0123456789','0000000000'),'0','') is null 

Malonylurea