今天看到ＰＧＳＱＬ的函数这一节。
POSTGREsql 相比MysqL多了好多实用的函数。
1、正则替换。@H_403_7@
t_girl=@H_403_7@#@H_403_7@ create@H_403_7@ table@H_403_7@ t_regexp(@H_403_7@id serial not@H_403_7@ null@H_403_7@,@H_403_7@username char@H_403_7@(@H_403_7@20)@H_403_7@ )@H_403_7@;@H_403_7@
NOTICE:@H_403_7@ CREATE@H_403_7@ TABLE@H_403_7@ will create@H_403_7@ implicit sequence@H_403_7@ "t_regexp_id_seq"@H_403_7@ for@H_403_7@ serial column@H_403_7@ "t_regexp.id"@H_403_7@
TABLE@H_403_7@
Time:@H_403_7@ 6.@H_403_7@148 ms
t_girlinsert@H_403_7@ into@H_403_7@ t_regexp(@H_403_7@usernamevalues@H_403_7@ (@H_403_7@'Shit,shit'@H_403_7@403_7@'i I i I you'@H_403_7@;@H_403_7@
INSERT@H_403_7@ 0 2
Time:@H_403_7@ 2.@H_403_7@893 ms
t_girlselect@H_403_7@ *@H_403_7@ from@H_403_7@ t_regexp;@H_403_7@
id |@H_403_7@ username
----+----------------------
@H_403_7@
1 |@H_403_7@ Shit403_7@shit
2 |@H_403_7@ i I i I you
(@H_403_7@2 rows@H_403_7@)@H_403_7@
Time:@H_403_7@ 0.@H_403_7@296 ms
t_girlupdate@H_403_7@ t_regexp set@H_403_7@ username =@H_403_7@ regexp_replace403_7@'[Ss]'@H_403_7@'a'@H_403_7@'g'@H_403_7@UPDATE@H_403_7@ 2
Time:@H_403_7@ 1.@H_403_7@139 ms
t_girl|@H_403_7@ ahit403_7@ahit
2 .@H_403_7@271 ms
t_girl'a(..)'@H_403_7@403_7@E'S\\1P'@H_403_7@.@H_403_7@090 ms
t_girl|@H_403_7@ ShiPt403_7@ShiPt
2 .@H_403_7@289 ms
t_girl#@H_403_7@
可惜MysqL的REPLACE函数只能简单的替换字符串。
以往碰到这种正则替换，先把数据导出到文本，然后再外部用正则替换掉，再导入到数据库中。
2、字符分割。
获取分割指定的字符串部分，这个在ＭＹＳＱＬ中也是只能用循环解决。
@H_403_7@Time.@H_403_7@306 ms
t_girlselect@H_403_7@ split_part'now,year,month'@H_403_7@','@H_403_7@403_7@2;@H_403_7@
split_part
------------
@H_403_7@
year
(@H_403_7@1 row@H_403_7@.@H_403_7@328 ms
t_girl403_7@3------------
@H_403_7@
month
.@H_403_7@253 ms
t_girl#@H_403_7@
这个在MysqL中只能通过写一个循环来判断。
至于双方的效率暂时还没有测试。
3、顺序替换字符，这个也是MysqL中没有的。@H_403_7@
t_girlselect@H_403_7@ translate@H_403_7@'mo'@H_403_7@'ab'@H_403_7@;@H_403_7@
translate@H_403_7@
----------------
@H_403_7@
nbw403_7@year403_7@abnth
.@H_403_7@309 ms
4、截取符合正则的子串。@H_403_7@
t_girlSELECT@H_403_7@ SUBSTRING'XY1234Z'@H_403_7@403_7@ 'Y*?([0-9]{1,3})'@H_403_7@;@H_403_7@
substring
-----------
@H_403_7@
1
.@H_403_7@246 ms
t_girl'Y*?([0-9]{2,0);">-----------
@H_403_7@
12
(1 row)@H_403_7@
5、等待更新。。。@H_403_7@@H_403_7@
