MySQL的replace()函数介绍
今天在工作的过程中碰到一个问题,要把数据库中某个列的所有值中含有"shop.xxxx.net"的字符更换成"www.nowamagic.net",本来可以写个脚本,把所有的值都取出再用php进行处理,但是那样就效率非常低了,想到看试下能不能直接在MySQL中用SQL语句直接来处理,经过一番搜索,终于找到解决方案,其实最重要的是mysql的replace函数,关于这个函数的介绍,我在MySQL手册中是没看懂,不过能实现我想要的功能就行。
下面就是对这个函数的简要介绍以及范例。
比如你要将 表 tb1里面的 f1字段的abc替换为def:
1
UPDATE
tb1
SET
f1=
REPLACE
(f1,
'abc'
,
'def'
);
2
REPLACE
(str,from_str,to_str)
在字符串 str 中所有出现的字符串 from_str 均被 to_str替换,然后返回这个字符串:
1
mysql>
SELECT
REPLACE
(
'www.mysql.com'
,
'w'
,
'Ww'
);
2
->
'WwWwWw.mysql.com'
这个函数是多字节安全的。
示例:
1
UPDATE
`dede_addonarticle`
SET
body =
REPLACE
( body,
'</td>'
,
" );
2
UPDATE `dede_addonarticle` SET body = REPLACE ( body,'</tr>',"
);
3
UPDATE
`dede_addonarticle`
SET
body =
REPLACE
( body,
'<tr>'
,
" );
4
UPDATE `dede_archives` SET title= REPLACE ( title,'简明现代魔法 – ',"
);
5
UPDATE
`dede_addonarticle`
SET
body =
REPLACE
( body,
'../../../../../../'
,
'http://special.dayoo.com/meal/'
);
用法1.replace intoreplace into table (id,name) values('1','aa'),('2','bb')
此语句的作用是向表table中插入两条记录。
2.replace(object, search,replace)
把object中出现search的全部替换为replaceselect replace('www.163.com','w','Ww')—>WwW wWw.163.com
例:把表table中的name字段中的 aa替换为bbupdate table set name=replace(name,'aa','bb')
刚开始,Update AA 表 Set xx字段=Replace(xx字段,"要替换的","特定串") ,出现错误:函数 replace 的参数 1 的数据类型 ntext 无效。Update article set heading=Replace(convert(nvarchar(4000),heading),'<script></script>','')
1
update
表名
2
set
text类型字段名=
replace
(
convert
(
varchar
(8000),text类型字段名),
'要替换的字符'
,
'替换成的值'
)
varchar和nvarchar类型是支持replace,所以如果你的text/ntext不超过8000/4000可以先转换成前面两种类型再使用replace。
1
update
表名
2
set
text类型字段名=
replace
(
convert
(
varchar
(8000),text类型字段名),
'要替换的字符'
,
'替换成的值'
)
1
update
表名
2
set
ntext类型字段名=
replace
(
convert
(nvarchar(4000),ntext类型字段名),
'要替换的字符'
,
'替换成的值'
)
如果text/ntext超过8000/4000,看如下例子:
view sourceprint?01
declare
@pos
int
02
declare
@len
int
03
declare
@str nvarchar(4000)
04
declare
@des nvarchar(4000)
05
declare
@
count
int
06
set
@des =
'<requested_amount+1>'
--要替换成的值
07
08
set
@len=len(@des)
09
set
@str=
'<requested_amount>'
--要替换的字符
10
11
12
set
@
count
=0
--统计次数.
13
14
15
WHILE 1=1
16
BEGIN
17
select
@pos=patINDEX(
'%'
+@des+
'%'
,propxmldata) - 1
18
from
表名
19
where
条件
20
21
IF @pos>=0
22
begin
23
DECLARE
@ptrval
binary
(16)
24
SELECT
@ptrval = TEXTPTR(字段名)
25
from
表名
26
where
条件
27
UPDATETEXT 表名.字段名 @ptrval @pos @len @str
28
set
@
count
=@
count
+1
29
end
30
ELSE
31
break;
32
END
33
34
select
@
count