直接用 update D04 set D0403='XX,YY,AA,BB,CC,ZZ' where ...
where 后面的条件呢?
declare @string1 varchar(50),@string2 varchar(50) set @string1='xx,yy,zz,tt' set @string2='xx,yy,aa,bb,cc,zz'select @string1=@string1+',',@string2=@string2+',' --实现部分 select @string1+left(replace(replace(replace(replace(@string2,'xx,',''),'yy,',''),'zz,',''),'tt,',''),len(replace(replace(replace(replace(@string2,'xx,',''),'yy,',''),'zz,',''),'tt,',''))-1)
1。在前台判断每一个单独的子串是否在里面,然后可以直接更新 2。用function
用一条SQL语句来现实很困难,为什么不能用函数呢?
--写一函数: 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')
DECLARE @STRING VARCHAR(50) SET @STRING='xx,yy,aa,bb,cc,zz' SET @STRING=','+@STRINGSELECT replace(replace(replace(replace(@string,',xx',''),',yy',''),',zz',''),',tt','')update d04 set D0403=D0403+replace(replace(replace(replace(@string,',xx',''),',yy',''),',zz',''),',tt','')
你的D0403如果是固定的'xx,yy,zz,tt'的话,用上面的语句,一条就可以了,如果不是固定的,就用自定义函数: -- ============================================= -- Create inline function (IF) -- ============================================= IF EXISTS (SELECT * FROM sysobjects WHERE name = N'test') DROP FUNCTION test GOCREATE 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 GOselect dbo.test('ab,cd,ef','ab,ef,dd,cc') select dbo.test('xx,yy,zz,tt','xx,yy,aa,bb,cc,zz')
to: wanyingsong(豌豆) 你写的SQL语句,虽然能得到结果,但是有局限性呀?因为我的变量值不是固定死的,它的值是不能确定的,只不过它的格式是固定的。所以还希望你能不能改进一下。谢谢!
只要你的D0403的值是'xx,yy,zz,tt'不变,任何变量只要它是以'xx,yy,aa,bb'这种格式的,用我的语句都是可以的呀 DECLARE @STRING VARCHAR(50) SET @STRING='xx,yy,aa,bb,cc,zz' SELECT 'xx,yy,zz,tt'+replace(replace(replace(replace(','+@string,',xx',''),',yy',''),',zz',''),',tt','')SET @STRING='xx,yy,aa,bb,ff,gg,ee,ii,xy,cc,zz'SELECT 'xx,yy,zz,tt'+replace(replace(replace(replace(','+@string,',xx',''),',yy',''),',zz',''),',tt','')
update D04 set D0403='XX,YY,AA,BB,CC,ZZ'
where ...
set @string1='xx,yy,zz,tt'
set @string2='xx,yy,aa,bb,cc,zz'select @string1=@string1+',',@string2=@string2+','
--实现部分
select @string1+left(replace(replace(replace(replace(@string2,'xx,',''),'yy,',''),'zz,',''),'tt,',''),len(replace(replace(replace(replace(@string2,'xx,',''),'yy,',''),'zz,',''),'tt,',''))-1)
2。用function
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')
SET @STRING='xx,yy,aa,bb,cc,zz'
SET @STRING=','+@STRINGSELECT replace(replace(replace(replace(@string,',xx',''),',yy',''),',zz',''),',tt','')update d04 set D0403=D0403+replace(replace(replace(replace(@string,',xx',''),',yy',''),',zz',''),',tt','')
-- =============================================
-- Create inline function (IF)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'test')
DROP FUNCTION test
GOCREATE 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
GOselect dbo.test('ab,cd,ef','ab,ef,dd,cc')
select dbo.test('xx,yy,zz,tt','xx,yy,aa,bb,cc,zz')
你写的SQL语句,虽然能得到结果,但是有局限性呀?因为我的变量值不是固定死的,它的值是不能确定的,只不过它的格式是固定的。所以还希望你能不能改进一下。谢谢!
DECLARE @STRING VARCHAR(50)
SET @STRING='xx,yy,aa,bb,cc,zz' SELECT 'xx,yy,zz,tt'+replace(replace(replace(replace(','+@string,',xx',''),',yy',''),',zz',''),',tt','')SET @STRING='xx,yy,aa,bb,ff,gg,ee,ii,xy,cc,zz'SELECT 'xx,yy,zz,tt'+replace(replace(replace(replace(','+@string,',xx',''),',yy',''),',zz',''),',tt','')
DECLARE @STRING VARCHAR(50),@D0403 VARCHAR(50)
SET @D0403='11,xx,33,zz'SET @STRING='xx,yy,aa,bb,cc,zz' SELECT @d0403+replace(replace(replace(replace(','+@string,','+left(@D0403,2),''),substring(@D0403,3,3),''),substring(@D0403,6,3),''),substring(@D0403,9,3),'')SET @STRING='xx,yy,aa,bb,ff,gg,ee,ii,xy,cc,zz'SELECT @d0403+replace(replace(replace(replace(','+@string,','+left(@D0403,2),''),substring(@D0403,3,3),''),substring(@D0403,6,3),''),substring(@D0403,9,3),'')
我的D0403和另外一个变量的值不是固定的。有可能是'11,xx,33,zz',也有可能是'1,xx,33,zzz',更有可能是'z,TTTT,zz'