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'  '1235' ; insert Employee select 2 'B'  '2' ; insert Employee select 3 'C'  '45' ; 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