mssql比较where子句中的两个多值字符串

I want to compare two multiple value strings with each other to see if one of the values exists in the other string.

我想比较两个多值字符串,以查看另一个字符串中是否存在其中一个值。

I have a table with a nvarchar row with pipe separated values, e.g.

我有一个带有nvarchar行的表,其中包含管道分隔值,例如

'value1|value2|value3'

I also have an nvarchar variable with a comma separated string, e.g.

我还有一个带有逗号分隔字符串的nvarchar变量,例如

'value2,value3'

until now the column in the table had one value, I used a table function to spit the string in the variable and used the IN clause to see if the value was in the generated table. e.g.

到目前为止,表中的列有一个值,我使用表函数在变量中吐出字符串,并使用IN子句查看该值是否在生成的表中。例如

select * from table1
    WHERE column in (select val from dbo.split(@variable,','))

this won't work if the column also contains more values.

如果列还包含更多值,则无效。

select * from table1    
    WHERE (select val from dbo.split(column,'|')) in (select val from dbo.split(@variable,','))

here it tries to compare 2 generated tables with each other which fails. I have tried this using joins, but can't find a way to properly do this. I'm using MSSQL 2008R2

这里它试图将2个生成的表相互比较失败。我已尝试使用连接,但无法找到正确执行此操作的方法。我正在使用MSSQL 2008R2

1 个解决方案

#1

Maybe this can help you:

也许这可以帮助你:

select * from table1 where exists 
(select * from 
    (select val from dbo.split(table1.column,'|')) a,
    (select val from dbo.split(@variable,',')) b 
where a.val=b.val)