如何从SQL查询的DescriptionMsg字段中提取单引号内的动态字符串

实现思路 #

• 目标值固定在Account Number is changed from之后的两对单引号内，分别为修改前旧账号、修改后新账号
• 通过字符串定位函数找到四个单引号的位置，再用截取函数取两对单引号中间的内容即可批量处理所有符合条件的记录

各主流数据库实现代码 #

MySQL 版本 #

SELECT 
    Logid, 
    mrd_id, 
    mrd_accountnumber, 
    DescriptionMsg,
    -- 提取旧账号：第一对单引号之间的内容
    SUBSTRING(
        DescriptionMsg,
        LOCATE("'", DescriptionMsg) + 1,
        LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg) + 1) - LOCATE("'", DescriptionMsg) - 1
    ) AS old_account_number,
    -- 提取新账号：第二对单引号之间的内容
    SUBSTRING(
        DescriptionMsg,
        LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg) + 1) + 1) + 1,
        LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg) + 1) + 1) + 1) 
        - LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg, LOCATE("'", DescriptionMsg) + 1) + 1) - 1
    ) AS new_account_number
FROM RecordData_Log
WHERE Log_date > '2020-12-31 23:59:59.999'
AND Log_type = 'Record Updated'
AND DescriptionMsg LIKE '%Account Number is changed from%';

SQL Server 版本 #

SELECT 
    Logid, 
    mrd_id, 
    mrd_accountnumber, 
    DescriptionMsg,
    -- 提取旧账号
    SUBSTRING(
        DescriptionMsg,
        CHARINDEX('''', DescriptionMsg) + 1,
        CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg) + 1) - CHARINDEX('''', DescriptionMsg) - 1
    ) AS old_account_number,
    -- 提取新账号
    SUBSTRING(
        DescriptionMsg,
        CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg) + 1) + 1) + 1,
        CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg) + 1) + 1) + 1)
        - CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg, CHARINDEX('''', DescriptionMsg) + 1) + 1) - 1
    ) AS new_account_number
FROM RecordData_Log
WHERE Log_date > '2020-12-31 23:59:59.999'
AND Log_type = 'Record Updated'
AND DescriptionMsg LIKE '%Account Number is changed from%';

PostgreSQL 版本 #

SELECT 
    Logid, 
    mrd_id, 
    mrd_accountnumber, 
    DescriptionMsg,
    -- 正则匹配第一组单引号内的数值
    (regexp_match(DescriptionMsg, $$'(\d+)'$$))[1] AS old_account_number,
    -- 正则匹配第二组单引号内的数值
    (regexp_match(DescriptionMsg, $$'(\d+)'$$))[2] AS new_account_number
FROM RecordData_Log
WHERE Log_date > '2020-12-31 23:59:59.999'
AND Log_type = 'Record Updated'
AND DescriptionMsg LIKE '%Account Number is changed from%';

注意：如果单引号内可能包含非数字内容，PostgreSQL版本正则的\d+替换为[^']+即可适配所有场景，MySQL和SQL Server的截取逻辑本身兼容任意单引号内内容，无需调整。

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