mysql -替换字符串里面的字符

昨日，因为项目升级上线测试，需要把图片的地址里面的字符串改为ip形式，做了一下小测试，写了一个sql。记录如下：

原图片路径：photo_path=’http://hi-image.mobi.net/mss/images/201303142345678.jpg’

需要把图片路径里面的hi-image.mobi.net改为88.88.888.888形式。

测试的sql如下：

--创建测试表test_cx
create table test_cx (photo_path VARCHAR(100));

--插入数据：

Insert into test_cx values (‘http://hi-image.mobi.net/mss/images/201303142345678.jpg’);

Commit;

--查询sql：

select concat(left(photo_path,7),

REPLACE('88.88.888.888','hi-image.mobi5.net',photo_path),

mid(photo_path,25)

) from test_cx;

结果如下：
http://88.88.888.888/mss/images/201303142345678.jpg

 

--更新表列值：

update test_cx

set 

photo_path=concat(left(photo_path,7),

REPLACE('88.88.888.888','hi-image.mobi5.net',photo_path),

mid(photo_path,25));

commit;

--查询表：

select * from test_cx;

结果如下，字符串替换为ip地址

http://88.88.888.888/mss/images/201303142345678.jpg

 

附录，官方文档函数说明：

1.mid() 

mie(str,pos,len)   return a substring starting from the specified position

mid(str,pos,len)  is a synonym for  substring(str,pos,len). –mid是substring函数的同义词。注，mid函数的索引值从1开始，不是0.

2.left()

Left()  return the leftmost number of characters as specified

left(str,len) returns the leftmostlen characters from the stringstr, ornull if any argument isnull.

3. replace()

replace()  replace occurrences of a specified string

replace(str,from_str,to_str)  returns the stringstr with all occurrences of the stringfrom_str replaced by the stringto_str.  replace()  performs a case-sensitive match when searching forfrom_str.

4. concat()

concat()  return concatenated string

concat(str1,str2.....) returns the string that results from concatenating the arguments. may have one or more arguments. if all arguments are nonbinary strings, the result is a nonbinary string. if the arguments include any binary strings, the result is a binary string. a numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast,