第1个回答 2009-11-03
查询语句为 select * from dv_bbs1 where Body like "?/8872x/?";
求截取字符串并将其替换的 sql语句
你这个字段是否有规律?比如/8872x/前台有固定的字符数量??
update dv_bbs1 set Body="/8872PL/"
where Body="/8872X/"
第2个回答 2009-11-03
update dv_bbs1 set Body=replace(Body,'/8872X/','/8872PL/') where Body like '%/8872X/%'
这样不可以吗?
根本不用截取再替换啊?他自己找到这个字符串替换为一个字符串。我都执行N篇了.
SQL支持批量替换的。你想复杂了吧
第3个回答 2009-11-03
不需要subString只要replace就行了
update dv_bbs1 set body=replace(body,'/8872X/','8872PL/');
replace的用法:replace(f,str1,str2)
f字段用str1替换为str2
第4个回答 推荐于2016-02-04
update dv_bbs1
set body= REPLACE(body,'/8872X/','/8872PL/')
把ntext转换为nvarchar, 然后再replace用下面的:
declare @t table (id int,body Ntext)
insert into @t
select 1,N'qqqqqqqqqqqqqqqqqqqqqqqq/8872X/' union
select 2,N'/8872X/wwwwwwwwwwwwwwwwwwwwwww' union
select 3,N'1/8872X/1'
select * from @t
update @t
set body= REPLACE(cast(body as nvarchar),'/8872X/','/8872PL/')
select * from @t
--结果---
(3 row(s) affected)
id body
----------- -----------------
1 qqqqqqqqqqqqqqqqqqqqqqqq/8872X/
2 /8872X/wwwwwwwwwwwwwwwwwwwwwww
3 1/8872X/1
(3 row(s) affected)
(3 row(s) affected)
id body
----------- -------------------
1 qqqqqqqqqqqqqqqqqqqqqqqq/8872X
2 /8872PL/wwwwwwwwwwwwwwwwwwwwwww
3 1/8872PL/1
(3 row(s) affected)本回答被提问者采纳