如何提取格式为0000a000000aaaaaaa的数据库子串？

这个场景我碰到过好多次——靠固定前缀或者位置提取子串确实不靠谱，尤其是当格式固定但位置飘移的时候，正则表达式才是解决这类问题的正道。下面我针对主流的数据库分别给出具体的实现方案，你可以根据自己用的数据库直接套用：

1. SQL Server #

SQL Server可以用PATINDEX匹配固定格式的模式，再结合SUBSTRING提取子串。因为老版本的SQL Server不支持正则的重复量词（比如{4}），所以我们直接把格式展开写：

SELECT 
    SUBSTRING(t.jobName, PATINDEX('%[0-9][0-9][0-9][0-9][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z]%', t.jobName), 18) AS Code
FROM table t
WHERE PATINDEX('%[0-9][0-9][0-9][0-9][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z]%', t.jobName) > 0

• 解释：PATINDEX会找到第一个符合「4位数字+1位字母+6位数字+7位字母」格式的子串起始位置，SUBSTRING从这个位置开始取18个字符（4+1+6+7=18）。WHERE子句用来过滤掉没有匹配到目标格式的行，避免返回无效值。

2. MySQL / MariaDB #

MySQL和MariaDB直接支持REGEXP_SUBSTR函数，可以一步提取符合正则的子串：

SELECT 
    REGEXP_SUBSTR(t.jobName, '[0-9]{4}[a-zA-Z][0-9]{6}[a-zA-Z]{7}') AS Code
FROM table t
WHERE t.jobName REGEXP '[0-9]{4}[a-zA-Z][0-9]{6}[a-zA-Z]{7}'

• 解释：REGEXP_SUBSTR会自动返回第一个匹配目标格式的子串，WHERE子句过滤掉不包含目标格式的行。

3. Oracle #

Oracle同样使用REGEXP_SUBSTR函数，搭配REGEXP_LIKE做过滤：

SELECT 
    REGEXP_SUBSTR(t.jobName, '[0-9]{4}[a-zA-Z][0-9]{6}[a-zA-Z]{7}') AS Code
FROM table t
WHERE REGEXP_LIKE(t.jobName, '[0-9]{4}[a-zA-Z][0-9]{6}[a-zA-Z]{7}')

4. PostgreSQL #

PostgreSQL用SUBSTRING结合正则匹配来提取，用~操作符做过滤：

SELECT 
    SUBSTRING(t.jobName FROM '[0-9]{4}[a-zA-Z][0-9]{6}[a-zA-Z]{7}') AS Code
FROM table t
WHERE t.jobName ~ '[0-9]{4}[a-zA-Z][0-9]{6}[a-zA-Z]{7}'

补充说明 #

如果你的字符串中可能存在多个符合该格式的子串，上面的方案只会提取第一个。如果需要提取所有匹配的子串，不同数据库的处理方式略有不同（比如SQL Server需要用递归CTE循环提取），但根据你给出的示例，应该只有一个目标子串，所以上面的方案完全够用。

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