比较sql中的逗号分隔值 [英] compare comma separated values in sql
本文介绍了比较sql中的逗号分隔值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想写一个函数来比较SQL中的逗号分隔值
我从互联网上取了一些代码:
I am trying to write a function to compare comma separated values in SQL I've taken some code from Internet :
SELECT CASE WHEN EXISTS
(
SELECT 1 FROM dbo.Split(@v1)
WHERE ', ' + LTRIM(@v2) + ','
LIKE '%, ' + LTRIM(Item) + ',%'
) THEN 1 ELSE 0 END;
然后我创建一个函数:
CREATE FUNCTION [dbo].[fnCompareCSVString]
(
@str1 nvarchar(50),
@str2 nvarchar(50)
)
RETURNS int
AS
BEGIN
SELECT CASE WHEN EXISTS
(
SELECT 1 FROM dbo.Split(@str1)
WHERE ', ' + LTRIM(@str2) + ','
LIKE '%, ' + LTRIM(Item) + ',%'
) THEN 1 ELSE 0 END;
END
我不善于SQL我知道这是错误的
I am not good in SQL I know this is wrong
我想写一个函数来比较逗号分隔值,比较后将获取两个值(逗号分隔值)返回值将为true或false
I want to write a function for comparing comma separated values that will take two values (comma separated values) after comparison the return value will be true or false
在这个SQL函数中我需要做什么修改?
What changes I have to do in this SQL function ?
推荐答案
这是您要找的吗?
True / False结果 b
-- matches only those values which exist in both CSV sets
SELECT T1.[Item], CASE WHEN T2.[Item] IS NULL THEN 0 ELSE 1 END AS [Match]
FROM [dbo].[Split]('val1,val2,val3', ',') AS T1
LEFT JOIN [dbo].[Split]('val3,val4', ',') AS T2 on T1.[Item] = T2.[Item]
返回
Item Match
val1 0
val2 0
val3 1
只有真实匹配
Only true matches
-- matches only those values which exist in both CSV sets
SELECT T1.[Item]
FROM [dbo].[Split]('val1,val2,val3', ',') AS T1
INNER JOIN [dbo].[Split]('val3,val4', ',') AS T2 on T1.[Item] = T2.[Item]
返回
Item
val3
拆分功能 b
$ b
Split function
CREATE FUNCTION [dbo].[Split]
(
@s VARCHAR(max),
@split CHAR(1)
)
RETURNS @temptable TABLE ([Item] VARCHAR(MAX))
AS
BEGIN
DECLARE @x XML
SELECT @x = CONVERT(xml,'<root><s>' + REPLACE(@s,@split,'</s><s>') + '</s></root>');
INSERT INTO @temptable
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c);
RETURN
END;
这篇关于比较sql中的逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文