gpt4 book ai didi

sql - 比较sql中的逗号分隔值

转载 作者:行者123 更新时间:2023-12-05 06:46:14 24 4
gpt4 key购买 nike

我正在尝试编写一个函数来比较 SQL 中的逗号分隔值我从互联网上获取了一些代码:

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 我知道这是错误的

问题:

我想写一个比较逗号分隔值的函数,比较后将取两个值(逗号分隔值),返回值将为真或假

我必须在此 SQL 函数中做哪些更改?

最佳答案

这是您要找的吗?

真/假结果

-- 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

只有真实匹配

-- 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

拆分函数

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 - 比较sql中的逗号分隔值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17653163/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com