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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With