16

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

John Woo
  • 258,903
  • 69
  • 498
  • 492
JetJack
  • 978
  • 8
  • 26
  • 51

6 Answers6

29

Try ISNUMERIC

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

SQLFiddle Demo

John Woo
  • 258,903
  • 69
  • 498
  • 492
6
SELECT * FROM @Table 
WHERE Col NOT LIKE '%[^0-9]%'
highwingers
  • 1,649
  • 4
  • 21
  • 39
  • SELECT * FROM @Table WHERE Col NOT LIKE '%[^0-9.-]%' – highwingers Sep 30 '12 at 21:16
  • 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* :-) . – GilM Oct 01 '12 at 01:00
  • yes GilM, I never relied on isNumeric function in SQL or Asp Classic. – highwingers Oct 01 '12 at 03:33
1

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
Void Ray
  • 9,849
  • 4
  • 33
  • 53
0
 SELECT column1 FROM table where ISNUMERIC(column1) = 1
Raj Adroit
  • 3,828
  • 5
  • 31
  • 44
0

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
sumeyrad
  • 11
  • 1
0

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

סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Alex Fong
  • 43
  • 1
  • 1
  • 3