REGEXP_SUBSTR('4550 Montgomery Avenue*Toronto*Ontario*M4A 2S3*', '(.*? ){2}(.*?),')
我得到一个动态文本列表,其中包含以*分隔的地址、城市、州和邮政编码。需要使用REGEXP_SUBSTR精确地址、城市、州和邮政编码。
我将使用SUBSTRING_INDEX(),因为对于此任务,它比正则表达式更简单。
mysql> set @string = '4550 Montgomery Avenue*Toronto*Ontario*M4A 2S3*';
mysql> select
substring_index(@string, '*', 1) as address,
substring_index(substring_index(@string, '*', 2), '*', -1) as city,
substring_index(substring_index(@string, '*', 3), '*', -1) as state_province,
substring_index(substring_index(@string, '*', 4), '*', -1) as postal_code;
+------------------------+---------+----------------+-------------+
| address | city | state_province | postal_code |
+------------------------+---------+----------------+-------------+
| 4550 Montgomery Avenue | Toronto | Ontario | M4A 2S3 |
+------------------------+---------+----------------+-------------+