你的D0403如果是固定的'xx,yy,zz,tt'的话,用上面的语句,一条就可以了,如果不是固定的,就用自定义函数:
-- =============================================
-- Create inline function (IF)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'test')
DROP FUNCTION test
GO
CREATE FUNCTION test(
@string1 varchar(40),
@string2 varchar(40))
RETURNS varchar(40)
AS
begin
declare @table table(string varchar(10))
declare @string varchar(40)
select @string=@string1+','+@string2+','
while len(@string)>=3
begin
insert into @table select left(@string,3)
select @string=right(@string,len(@string)-3)
end
select @string=''
select @string=@string+string from @table group by string
return left(@string,len(@string)-1)
end
GO
--写一函数:
CREATE FUNCTION dbo.f_1(@str1 varchar(8000),@str2 varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @t table(s varchar(100))
DECLARE @s varchar(8000)
SET @s=@str1
WHILE CHARINDEX(',',@s)>0
BEGIN
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(',',@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(',',@s))
END
INSERT INTO @t(s) VALUES(@s)
SET @s=@str2
WHILE CHARINDEX(',',@s)>0
BEGIN
IF LEFT(@s,CHARINDEX(',',@s)-1) not in(SELECT s FROM @t)
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(',',@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(',',@s))
END
IF @s not in(SELECT s FROM @t)
INSERT INTO @t(s) VALUES(@s)
SET @s=''
SELECT @s=@s+','+s FROM @t
RETURN(right(@s,len(@s)-1))
END
GO
--调用
print dbo.f_1('XX,YY,ZZ,TT','XX,YY,AA,BB,CC,ZZ')
--更新表
update D04 set D0403=dbo.f_1(D0403,'XX,YY,AA,BB,CC,ZZ')