我最近升级到mysql 8,以便可以使用新的正则表达式函数(https://dev.mysql.com/doc/refman/8.0/en/regexp.html)清理很多坏地址。但是,我在使用regexp\u replace时遇到了麻烦。
我首先以运行select查询为例(这样在运行update查询之前就可以看到发生了什么):
SELECT address1_raw, CONVERT(REGEXP_REPLACE (address1_raw, '^[0-9]+ ', '')
USING UTF8) as replaced,
CONVERT(REGEXP_SUBSTR(address1_raw, '^[0-9]+ ') USING UTF8) as captured
FROM members WHERE REGEXP_LIKE (address1_raw, '^[0-9]+ ');
我想要的结果如下:
address1_raw | replaced | captured
54 Main St | Main St | 54
1089 High St | High St | 1089
67 Broadway | Broadway | 67
相反,我得到的是:
address1_raw | replaced | captured
54 Main St | Main St | 54
1089 High St | Main StHigh St | 1089
67 Broadway | Main StHigh StBroadway | 67
我完全被难住了!你知道这是怎么回事吗?
暂无答案!
目前还没有任何答案,快来回答吧!