Loading

Filter Query in SQL

Filter Query in SQL

Basit Khan

9y846

Hi,

I have a table Employee

Emp_No int

Emp_Name nvarchar(50)

Emp_DeptNo nvarchar(150)

Emp_No	Emp_Name	Emp_DeptNo
1	A	1, 2 , 3, 5
2	B	2
3	C	4, 5

Here i want to filter by Emp_DeptNo Where Emp_DeptNo in (3, 5) then result will be

Emp_No	Emp_Name	Emp_DeptNo
1	A	1, 2 , 3, 5

3	C	4, 5

How to filter this

Thanks

Basit.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
First thing is your table is not normalized. Going ahead with design, you will face many problems. So I suggest you should seriously consider normalizing this table by creating another table with Emp_No and DeptNo where there will be separate row for each combination. This type of table is called junction table.

I am also surprised to see how an employee can belong to more than one department. Anyway.

Now, to answer your question, you can query as follows:

select *
from Employee
where (',' + ltrim(rtrim(Emp_DeptNo)) + ',' like '%,3,%' or',' + ltrim(rtrim(Emp_DeptNo)) + ',' like '%,5,%')

It is assumed that there are no spaces in the column Emp_DeptNo.
Ananth G9y ago
Dynamic query. you can use like this
Input
create table Employee(Emp_No int,Emp_Name varchar(20),Emp_DeptNo varchar(max))
insert Employee select 1,'A','1,2,3,5';
insert Employee select 2,'B','2';
insert Employee select 3,'C','4,5';
Output Query:
select distinct temp1.Emp_No, temp1.Emp_Name, temp1.Emp_DeptNo from (SELECT A.Emp_No, A.Emp_Name, A.Emp_DeptNo,
     Split.a.value('.', 'VARCHAR(100)') AS String
FROM  (SELECT Emp_No, Emp_Name, Emp_DeptNo,
         CAST ('' + REPLACE([Emp_DeptNo], ',', '') + '' AS XML) AS String
     FROM  Employee ) AS A CROSS APPLY String.nodes ('/M') AS Split(a)) temp1  where temp1.String in('3','5') ;
Basit Khan9y ago
ManyThanks