How to filter only numeric values in the SQL SERVER table?

In this article I am going to explain about to search only numeric record in the SQL SERVER Table column. If you store the numeric and varchar values in the varchar datatype column then need to get only numeric values that time this code snippet is help you to get those records.


If you store varchar or numeric values in the same field then need to filter numeric values some times use this technique in that time to filter record.
Try below example to get numeric values record only


Create table like below

--Create table
create table emp (eno varchar(50), empname varchar(50),sal bigint)

Insert some values in that table with varchar and numeric employee no.

insert into emp values('101','Ravindran','25000')
insert into emp values('102','James','18000')
insert into emp values('106','Mike','12000')
insert into emp values('M012','Manger','45000')
insert into emp values('MM2','Assitant Manager','12000')
insert into emp values('MMM','Clerk','8000')

Now we are going to filter only numeric employee number record using below query

select * from emp where ISNUMERIC(eno) = 1

If you want only filter record more than 105 eno then use like below query to find out . In this query I eliminate NULL, Varchar records and cast that eno to int then filter

--CAST varchar to nvarchar
select * from emp where ISNUMERIC(eno) = 1 and CAST(isnull(eno,0) as nvarchar) > 105


I hope this article is help you to know about filtered database field record based on numeric fields and apply conditions for those numeric fields and apply further filter too.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: