CODEWITHSUNDEEP
sql-server
Data Engineer
Data Engineer

Reputation: 33

How to do a where condition on a split comma-separated value

I want to do a where condition on the following table but I don't how to do a split and a test after I get the values from my Period colomn :

Code     Period
1        PER1
2        PER
3        ESN;PER_ESN;PER
4        PRN
5        PRN1;PRN2;PRN3;PRN4

AN example of the query is the following :

select code from Mytable where Period like 'PER_ESN'

Hope someone can help :)

Upvotes: 0

Views: 43

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44356

EDIT: Changed to handle parameter Disclaimer. Your table is not normalized and it will give bad performance:

DECLARE @param_period VARCHAR(200) = 'PER_ESN'
SET @param_period =';'+ REPLACE(REPLACE(@param_period,'_','[_]'),'%', '[%]') + ';'

SELECT code 
FROM Mytable 
WHERE ';' + Period + ';' LIKE @param_period

The reason for replacing wildchars is to avoid different text to be accepted in it's place.

Upvotes: 1

Related Questions