CREATE FUNCTION dbo.UnionValue (@Str1 VARCHAR(9), @Str2 VARCHAR(9))
RETURNS VARCHAR(9) AS
BEGIN
DECLARE @Str VARCHAR(9)
DECLARE @RetStr VARCHAR(1)
DECLARE @i INT
SET @Str = ''
SET @i = 1
WHILE (@i < 10)
BEGIN
IF (SUBSTRING(@Str1, @i, 1) + SUBSTRING(@Str2, @i, 1)) = '00'
SET @RetStr = '0'
ELSE
SET @RetStr = '1'
SET @Str = @Str +@RetStr
SET @i = @i + 1
END
RETURN @Str
END
CREATE FUNCTION Union_String
(
@UniouStr1 VARCHAR(20)
,@UniouStr2 VARCHAR(20)
,@UnionFlag INT -- =1 並集 =0 交集
)
RETURNS VARCHAR(20)
BEGIN
DECLARE @StrLength INT , @ReturnStr VARCHAR(20) ,@StrTemp VARCHAR(20)
SET @ReturnStr=''
SET @StrLength=LEN(@UniouStr1)
WHILE ( @StrLength>0 )
BEGIN
SET @StrTemp=LEFT(@UniouStr1,1) + LEFT(@UniouStr2,1)
IF ( @StrTemp= '00' OR ( @StrTemp<>'11'AND @UnionFlag=1) )
BEGIN
SET @ReturnStr=@ReturnStr+'0'
END
ELSE
BEGIN
SET @ReturnStr=@ReturnStr+'1'
END
--//----------------------------------------------------------
SET @StrLength=@StrLength-1
SET @UniouStr1=RIGHT(@UniouStr1,@StrLength)
SET @UniouStr2=RIGHT(@UniouStr2,@StrLength)
END
RETURN @ReturnStr
END