SQL如何移除字符串末尾非数字字符 提取含分机的有效电话号码

核心逻辑 #

不需要单独匹配ext/ex开头的分机号规则，只要截取字符串从开头到最后一个数字字符的所有内容，即可自动剔除末尾拼接的姓名部分，同时完整保留主机号、分机号前缀和分机号数字。
针对给出的样例数据，处理后的预期结果为：

• 1111111 → 1111111
• 1111111name → 1111111
• 1111111ext2222name → 1111111ext2222
• 1111111ex222name → 1111111ex222

不同数据库的实现写法 #

MySQL 8.0+/MariaDB 10.0.5+（支持正则截取） #

利用正则贪婪匹配规则，^.*[0-9]会自动匹配从字符串开头到最后一位数字的所有内容：

SELECT
  PhoneNumber,
  -- 无数字的异常数据可通过IFNULL自定义返回值，这里示例返回空字符串
  IFNULL(REGEXP_SUBSTR(PhoneNumber, '^.*[0-9]'), '') AS ValidPhone
FROM 你的表名;

PostgreSQL #

PG原生支持通过SUBSTRING...FROM语法提取正则匹配内容：

SELECT
  PhoneNumber,
  COALESCE(SUBSTRING(PhoneNumber FROM '^.*[0-9]'), '') AS ValidPhone
FROM 你的表名;

SQL Server（兼容所有主流版本） #

通过字符串反转+字符位置查找的方式定位最后一个数字的位置，不需要依赖正则支持：

SELECT
  PhoneNumber,
  CASE WHEN PATINDEX('%[0-9]%', PhoneNumber) = 0 THEN ''
       ELSE LEFT(PhoneNumber, LEN(PhoneNumber) - PATINDEX('%[0-9]%', REVERSE(PhoneNumber)) + 1)
  END AS ValidPhone
FROM 你的表名;

扩展说明 #

如果需要直接更新原表字段值，把查询逻辑套入UPDATE语句即可，可额外加过滤条件仅处理末尾为非数字的记录，提升执行效率，以MySQL为例：

UPDATE 你的表名
SET PhoneNumber = REGEXP_SUBSTR(PhoneNumber, '^.*[0-9]')
WHERE PhoneNumber REGEXP '[^0-9]$];

内容的提问来源于stack exchange，提问作者user8314628