如何拆分含可变长度账号与姓名的混合列？

针对你的场景——字段内容由可变长度的数字账号和字母姓名拼接而成，核心思路是找到数字与字母的分界点，通过正则表达式或字符串定位函数拆分两部分。以下是主流数据库的实现方案：

MySQL/MariaDB #

方法1：使用正则提取函数 #

直接匹配开头的数字部分和结尾的字母部分：

SELECT 
  REGEXP_SUBSTR(ID, '^[0-9]+') AS account_id,
  REGEXP_SUBSTR(ID, '[A-Za-z]+$') AS first_name
FROM bad_data;

方法2：定位分界点再截取 #

先找到第一个非数字字符的位置，再拆分：

SELECT
  SUBSTRING(ID, 1, LOCATE(REGEXP_SUBSTR(ID, '[^0-9]'), ID) - 1) AS account_id,
  SUBSTRING(ID, LOCATE(REGEXP_SUBSTR(ID, '[^0-9]'), ID)) AS first_name
FROM bad_data;

SQL Server #

使用PATINDEX定位第一个非数字字符的位置：

SELECT
  SUBSTRING(ID, 1, PATINDEX('%[^0-9]%', ID) - 1) AS account_id,
  SUBSTRING(ID, PATINDEX('%[^0-9]%', ID), LEN(ID)) AS first_name
FROM bad_data;

如果存在纯数字的字段记录（无姓名部分），可以添加判断避免报错：

SELECT
  CASE WHEN PATINDEX('%[^0-9]%', ID) > 0 
       THEN SUBSTRING(ID, 1, PATINDEX('%[^0-9]%', ID) - 1) 
       ELSE ID END AS account_id,
  CASE WHEN PATINDEX('%[^0-9]%', ID) > 0 
       THEN SUBSTRING(ID, PATINDEX('%[^0-9]%', ID), LEN(ID)) 
       ELSE '' END AS first_name
FROM bad_data;

PostgreSQL #

方法1：正则截取 #

SELECT
  SUBSTRING(ID FROM '^[0-9]+') AS account_id,
  SUBSTRING(ID FROM '[A-Za-z]+$') AS first_name
FROM bad_data;

方法2：定位分界点截取 #

SELECT
  SUBSTRING(ID, 1, STRPOS(ID, SUBSTRING(ID FROM '[^0-9]')) - 1) AS account_id,
  SUBSTRING(ID, STRPOS(ID, SUBSTRING(ID FROM '[^0-9]'))) AS first_name
FROM bad_data;

补充说明 #

如果姓名中包含非字母字符（比如连字符、点号），可以将正则中的[A-Za-z]+替换为[^0-9]+，匹配所有非数字字符作为姓名部分，适配更复杂的场景。

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