如何使用SQL SELECT查询匹配文本将单列内容拆分为两列

现有数据表包含名为Note / Reason的文本列，存储内容示例：

Note / Reason
test
test2
test  REASON:ANOTHER PROVIDER
this is a test  REASON:NO FITTER

需按规则拆分该列为Note、Reason两个字段：文本中以REASON:开头的片段归入Reason字段，其余内容归入Note字段，期望输出格式：

Note                        Reason
---------------------------------------------------
test
test2
test                        REASON:ANOTHER PROVIDER
this is a test              REASON:NO FITTER

核心逻辑是先定位REASON:关键词在原字符串中的位置，存在则按位置拆分前后两段，不存在则将全部内容放入Note字段、Reason字段留空。以下是各主流数据库可直接运行的查询语句：

• MySQL/MariaDB 写法

SELECT
  TRIM(
    CASE WHEN LOCATE('REASON:', `Note / Reason`) > 0
    THEN SUBSTRING(`Note / Reason`, 1, LOCATE('REASON:', `Note / Reason`) - 1)
    ELSE `Note / Reason` END
  ) AS Note,
  CASE WHEN LOCATE('REASON:', `Note / Reason`) > 0
  THEN TRIM(SUBSTRING(`Note / Reason`, LOCATE('REASON:', `Note / Reason`)))
  ELSE NULL END AS Reason
FROM your_table;

• PostgreSQL 写法

SELECT
  TRIM(SPLIT_PART("Note / Reason", 'REASON:', 1)) AS Note,
  CASE WHEN POSITION('REASON:' IN "Note / Reason") > 0
  THEN 'REASON:' || TRIM(SPLIT_PART("Note / Reason", 'REASON:', 2))
  ELSE NULL END AS Reason
FROM your_table;

• SQL Server 写法

SELECT
  TRIM(
    CASE WHEN CHARINDEX('REASON:', [Note / Reason]) > 0
    THEN LEFT([Note / Reason], CHARINDEX('REASON:', [Note / Reason]) - 1)
    ELSE [Note / Reason] END
  ) AS Note,
  CASE WHEN CHARINDEX('REASON:', [Note / Reason]) > 0
  THEN TRIM(SUBSTRING([Note / Reason], CHARINDEX('REASON:', [Note / Reason]), LEN([Note / Reason])))
  ELSE NULL END AS Reason
FROM your_table;

说明：语句中TRIM函数的作用是清除拆分后字段首尾多余的空格，如果不需要处理空格直接删除该函数即可。如果原文本中出现多个REASON:，上述逻辑会以第一次出现的位置作为拆分点，符合常规拆分需求。

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