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

ゝ一世哀愁。 2021-09-15 03:20 264阅读 0赞

第一个：取两个字符串的并集，如'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',',')