表中有一些类似操作日志的记录,如select * from customers where customerid='TCANG'delete from orders where employeeid=9insert into customers values('AYWOT','Pdodgtrwa Ikuqyyayj,Onomkn Jagerg','Lgnuuf Bjxjxg','Pipzfb Yczzkh 692','Iiyhgt','NI','00328','Lbwywwd','57-8689-33306113')update orders set freight=009.91 where orderid=17050我想把它们中所有的具体值都替换成我所指定的一个字符串,如SOMEVALUE:select * from customers where customerid=SOMEVALUEdelete from orders where employeeid=SOMEVALUEinsert into customers values(SOMEVALUE)update orders set freight=SOMEVALUE where orderid=SOMEVALUE我打算用游标对每条这种记录做某个操作,达到这个目的。有什么好的方案吗?由于要替换的内容长度、位置都不确定,所以似乎有些复杂。
set @SOMEVALUE = 'XXXXXXXXX'select * from customers where customerid=@SOMEVALUEdelete from orders where employeeid=@SOMEVALUEinsert into customers values(@SOMEVALUE)update orders set freight=@SOMEVALUE where orderid=@SOMEVALUE
其实还有一种情况,就是执行存储过程的语句。如:exec add 1,2 (结果为3)同样地,我想忽略掉它的两个参数1和2,变成exec add SOMEVALUE,SOMEVALUE怎么办?难不成要在SQL下写个词法分析器?OMG
我只写了替换等于的情况,其它情况楼主参照写:
*/declare @Test table (ha varchar(8000))
insert @Test select 'select * from customers where customerid=''TCANG'''
insert @Test select 'delete from orders where employeeid = 9'
insert @Test select 'update orders set freight= 009.91 where orderid =17050'while exists (select 1 from @Test where charindex(' =', ha) > 0) update @Test set ha = replace(ha, ' =', '=') where charindex(' =', ha) > 0
while exists (select 1 from @Test where charindex('= ', ha) > 0) update @Test set ha = replace(ha, '= ', '=') where charindex('= ', ha) > 0
update @Test set ha = replace(ha, '=', ' = ') where charindex('=', ha) > 0
update @Test set ha = ha + ' ' --这个空格很重要declare @Replace varchar(100)
set @Replace = 'SOMEVALUE'select * from @Test
/*
select * from customers where customerid = 'TCANG'
delete from orders where employeeid = 9
update orders set freight = 009.91 where orderid = 17050
*/while exists (select 1 from @Test where charindex(' = ', ha) > 0) update @Test set ha = stuff(ha, charindex(' = ', ha),charindex(' ', ha, charindex(' = ', ha) + 3) - charindex(' = ', ha), '=' + @Replace) where charindex(' = ', ha) > 0
select * from @Test
/*
select * from customers where customerid=SOMEVALUE
delete from orders where employeeid=SOMEVALUE
update orders set freight=SOMEVALUE where orderid=SOMEVALUE
*/