How to select only numeric values

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

Answers


Try ISNUMERIC

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

SQLFiddle Demo


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

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

 SELECT column1 FROM table where ISNUMERIC(column1) = 1

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 

Need Your Help

Make UIAlertView Button trigger function On Press

iphone objective-c xcode uialertview

Currently I am using the following code to present a UIAlertView:

Reading and writing from xls and xlsx excel file in java using Apache POI

java excel apache-poi

I am writing a program which needs to read and write from excel files, irrespective of the format(xls or xlsx).