如何比较两个用逗号分隔的列值? [英] How to compare two column values which are comma separated values?
问题描述
我有一个带有特定列的表,其中有一列包含逗号分隔的值,例如 test,exam,result,other .
I have one table with specific columns, in that there is a column which contains comma separated values like test,exam,result,other.
我将诸如 result,sample,unknown,extras 之类的字符串作为参数传递给存储过程.然后我想通过检查此字符串中的每个短语来获取相关记录.
I will pass a string like result,sample,unknown,extras as a parameter to the stored procedure. and then I want to get the related records by checking each and every phrase in this string.
例如:
表A
ID Name Words
1 samson test,exam,result,other
2 john sample,no query
3 smith tester,SE
现在我要搜索结果,样品,未知,其他
那么结果应该是
ID Name Words
1 samson test,exam,result,other
2 john sample,no query
因为第一条记录结果匹配,第二条记录 sample 匹配.
because in the first record result matched and in the second record sample matched.
推荐答案
这不是一个很好的设计.最好将Word拆分成一个单独的表(id,word).
That's not a great design, you know. Better to split Words off into a separate table (id, word).
也就是说,这应该可以解决问题:
That said, this should do the trick:
set nocount on
declare @words varchar(max) = 'result,sample,unknown,extras'
declare @split table (word varchar(64))
declare @word varchar(64), @start int, @end int, @stop int
-- string split in 8 lines
select @words += ',', @start = 1, @stop = len(@words)+1
while @start < @stop begin
select
@end = charindex(',',@words,@start)
, @word = rtrim(ltrim(substring(@words,@start,@end-@start)))
, @start = @end+1
insert @split values (@word)
end
select * from TableA a
where exists (
select * from @split w
where charindex(','+w.word+',',','+a.words+',') > 0
)
我可以为您提供这个功能而在DBA地狱中燃烧吗!
May I burn in DBA hell for providing you this!
替换了带有SUBSTRING切片的STUFF,在长列表上快了一个数量级.
replaced STUFF w/ SUBSTRING slicing, an order of magnitude faster on long lists.
这篇关于如何比较两个用逗号分隔的列值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!