如何在拆分逗号分隔值上执行where条件

时间:2021-06-20 00:16:38

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 :

我想在下表中执行where条件,但是在得到我的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 :)

希望有人可以帮助:)

1 个解决方案

#1


1  

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.

更换wildchars的原因是为了避免在它的位置接受不同的文本。

#1


1  

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.

更换wildchars的原因是为了避免在它的位置接受不同的文本。