如何使用substring从单列中拆分提取Internal ID和External ID两列

你的Comment列格式高度固定，可通过substring搭配字符串定位函数实现拆分，不同数据库的定位函数名称略有差异，以下是主流数据库的实现代码：

MySQL 实现 #

MySQL使用LOCATE()函数定位子串位置：

SELECT
  -- 截取Internal ID区间
  SUBSTRING(
    Comment,
    LOCATE('Internal message ID: ', Comment) + LENGTH('Internal message ID: '),
    LOCATE(' External message ID: ', Comment) - (LOCATE('Internal message ID: ', Comment) + LENGTH('Internal message ID: '))
  ) AS `Internal ID`,
  -- 截取External ID从指定位置到末尾
  SUBSTRING(
    Comment,
    LOCATE('External message ID: ', Comment) + LENGTH('External message ID: ')
  ) AS `External ID`
FROM 你的表名;

PostgreSQL 实现 #

PostgreSQL使用STRPOS()函数定位子串位置：

SELECT
  SUBSTRING(
    "Comment",
    STRPOS("Comment", 'Internal message ID: ') + LENGTH('Internal message ID: '),
    STRPOS("Comment", ' External message ID: ') - (STRPOS("Comment", 'Internal message ID: ') + LENGTH('Internal message ID: '))
  ) AS "Internal ID",
  SUBSTRING(
    "Comment",
    STRPOS("Comment", 'External message ID: ') + LENGTH('External message ID: ')
  ) AS "External ID"
FROM 你的表名;

SQL Server 实现 #

SQL Server使用CHARINDEX()函数定位子串位置：

SELECT
  SUBSTRING(
    Comment,
    CHARINDEX('Internal message ID: ', Comment) + LEN('Internal message ID: '),
    CHARINDEX(' External message ID: ', Comment) - (CHARINDEX('Internal message ID: ', Comment) + LEN('Internal message ID: '))
  ) AS [Internal ID],
  SUBSTRING(
    Comment,
    CHARINDEX('External message ID: ', Comment) + LEN('External message ID: '),
    LEN(Comment)
  ) AS [External ID]
FROM 你的表名;

如果需要持久化存储拆分结果，可先给原表新增两个字段，再用上述逻辑执行UPDATE更新数据即可。

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