第一个:取两个字符串的并集,如'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',',')
第三个:取差集。。。。
我没写