Filter Query in SQL

Question

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.

Nitin Sontakke · 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 G · Answer

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 Khan · Answer

ManyThanks

Filter Query in SQL

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)