CODEWITHSUNDEEP
sqlsql-server
Abhinav Singh
Abhinav Singh

Reputation: 41

How to fetch result set containing only numeric value from varchar column?

I have data in following format. Volume of data is above 100k. I have id column which is varchar(8). It can contain digits 0 to 9 and characters a to z(Small characters).

ID          Name
-------------------   
123         Abhinav
a12b        Rajesh
2bj5K       Manish
344         Utkarsh

I need to fetch only those ids which has numbers only.

I want result set AS 

ID         Name
----------------
123        Abhinav
344        Utkarsh

I have query. I want to know if this query will provide me best solution:

SELECT
    ID, Name
FROM  
    Emp
WHERE
    ID = UPPER(ID)

Upvotes: 1

Views: 1140

Answers (3)

roman
roman

Reputation: 117400

The correct way to get only those ID with numbers would be to use like comparisons:

select ID, Name
from Emp
where ID not like '%[^0-9]%'

This code will correctly filter out records with ID like 34-6 or 1.33, which will be considered as numeric by isnumeric() function. So if your data will look like this:

ID          Name
123         Abhinav
A12B        Rajesh
2BjK        Manish
344         Utkarsh
34-6        Utkarsh
1.33        Utkarsh

Here's outputs you get:

select ID, Name
from Emp
where ID not like '%[^0-9]%'

--------------
ID         Name
123        Abhinav
344        Utkarsh

And for isnumeric() it would be

select ID, Name
from Emp
where isnumeric(ID)=1

--------------
ID         Name
123        Abhinav
344        Utkarsh
34-5       Utkarsh
1.33       Utkarsh

sql fiddle demo

Upvotes: 1

Bohemian
Bohemian

Reputation: 425053

Use regex:

Select ID,Name
FROM Emp
Where ID not like '%[A-Z]%'

Upvotes: 2

kmatyaszek
kmatyaszek

Reputation: 19296

You should use ISNUMERIC (msdn):

SELECT ID, Name FROM Emp WHERE ISNUMERIC(ID)=1

Upvotes: 4

Related Questions