Need Query help in SQL Server [Resolved]

Posted by Sureshij under Sql Server on 2/21/2014 | Points: 10 | Views : 944 | Status : [Member] | Replies : 1
My table #temp is having IDs such as {1, 2, 3, 4, 5, 6, '3,4', '3,4,7'}

How to filter records for specific ID values? For example, the below query is giving 1 record in the result. But i need to get 4 records as follows:

--My Query
DECLARE @filter VARCHAR(100) = '3,4'
SELECT * FROM #temp WHERE Col1 in (@filter)

OUTPUT:
******
3,4


Expected records in the Output:
******************************
3
4
3,4
3,4,7

Please help me

ij


Responses

Posted by: Bandi on: 2/21/2014 [Member] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
Hi,

Go ahead with below query

--Sample Data
Create table #temp(col1 varchar(100))
insert #temp values('1'), ('7'),('3'), ('4'), ('3,4'), ('3,4,7'), ('4,5,7')

DECLARE @filter VARCHAR(100) = '3,4'

--Your Query
SELECT * FROM #temp WHERE Col1 in (@filter)

/*
OUTPUT:
col1
3,4

*/

--Modified Query
select * FROM #temp
where ',' + col1 + ',' LIKE '%,' + @filter + ',%'
OR ',' + @filter + ',' LIKE '%,' + col1 + ',%'

/*OUTPUT:

col1
3
4
3,4
3,4,7
*/

--Drop sample Data
DROP Table #temp


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response