取两个字符串的并集与交集的函数

本文介绍了一个SQL函数，用于处理字符串集合的并集和交集操作，包括创建自定义函数的过程及如何正确调用这些函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一个：取两个字符串的并集，如'1,2,3'和'2,5,9'并集为：'1,2,3,5,9'

CREATE FUNCTION dbo.bingSTR(@str1 VARCHAR(1000),@str2 VARCHAR(1000),@splitstr VARCHAR(10))
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @str VARCHAR(2000),@len INT,@i INT,@s VARCHAR(2000)
DECLARE @strOut VARCHAR(100),@err VARCHAR(100)
IF @splitstr='' BEGIN SELECT @err = '无分隔符' GOTO ERR END
IF CHARINDEX(@splitstr,@str1) = 0
BEGIN SELECT @err = '第一个字符串不含分隔符' GOTO ERR END
IF CHARINDEX(@splitstr,@str2) = 0
BEGIN SELECT @err = '第二个字符串不含分隔符' GOTO ERR END
ERR:
IF ISNULL(@err,'') <> '' RETURN(@err)
SELECT @str = @str1 + @splitstr + @str2 + @splitstr
SELECT @i = 1
SELECT @len = LEN(@str)
DECLARE @T table(strT VARCHAR(200))
SELECT @s = ''
SELECT @strOut = @splitstr
WHILE @i <= @len
BEGIN
IF SUBSTRING(@str,@i,1) <> @splitstr
BEGIN
SELECT @s = @s + SUBSTRING(@str,@i,1)
END
ELSE
BEGIN
INSERT INTO @T SELECT @s
SELECT @s = ''
END
SELECT @i = @i + 1
END
SELECT @strOut = @strOut + strT + @splitstr FROM (SELECT DISTINCT strT FROM @T) A
RETURN(SUBSTRING(@strOut,2,LEN(@strOut)-2))
END

调用方法：
SELECT DBO.bingSTR('2,B','B','')
SELECT DBO.bingSTR('a|b|c','a|b|d','|')
SELECT DBO.bingSTR('3,332,55,66,78','4,55,87,55',',')

第二个：取两个字符串的并集，如'1,2,3'和'2,5,9'交集为：'2'

DROP FUNCTION dbo.jiaoSTR
CREATE FUNCTION dbo.jiaoSTR(@str1 VARCHAR(1000),@str2 VARCHAR(1000),@splitstr VARCHAR(10))
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @str VARCHAR(2000),@len INT,@i INT,@s VARCHAR(2000)
DECLARE @strOut VARCHAR(100),@err VARCHAR(100)
IF @splitstr='' BEGIN SELECT @err = '无分隔符' GOTO ERR END
IF CHARINDEX(@splitstr,@str1) = 0
BEGIN SELECT @err = '第一个字符串不含分隔符' GOTO ERR END
IF CHARINDEX(@splitstr,@str2) = 0
BEGIN SELECT @err = '第二个字符串不含分隔符' GOTO ERR END
ERR:
IF ISNULL(@err,'') <> '' RETURN(@err)
SELECT @str = @str1 + @splitstr + @str2 + @splitstr
SELECT @i = 1
SELECT @len = LEN(@str)
DECLARE @T table(strT VARCHAR(200))
SELECT @s = ''
SELECT @strOut = @splitstr
WHILE @i <= @len
BEGIN
IF SUBSTRING(@str,@i,1) <> @splitstr
BEGIN
SELECT @s = @s + SUBSTRING(@str,@i,1)
END
ELSE
BEGIN
INSERT INTO @T SELECT @s
SELECT @s = ''
END
SELECT @i = @i + 1
END
SELECT @strOut = @strOut + strT + @splitstr FROM
(SELECT strT FROM @T GROUP BY strT HAVING COUNT(*)>1)A
IF LEN(@strOut) = 1 RETURN('NULL')
RETURN(SUBSTRING(@strOut,2,LEN(@strOut)-2))
END

调用方法：
SELECT DBO.jiaoSTR('2,B','B','')
SELECT DBO.jiaoSTR('a|b|c','a|b|d','|')
SELECT DBO.jiaoSTR('3,332,4f,55f,66,78','4,55F,87',',')

第三个：取差集。。。。
我没写