如何在PostgreSQL中严格解析userid-date-amount格式字段

要解决错误匹配问题，核心是精确定义每个字段的格式规则，避免模糊匹配。以下是修改后的查询方案：

关键调整点 #

• 用精确正则约束各部分格式：
  • userid：纯数字，用\d+
  • date：严格匹配YYYY/MM/DD格式，用\d{4}/\d{2}/\d{2}
  • amount：带千分位逗号的数字，用\d+(?:,\d+)*
• 用^和$确保整个Description字段完全符合目标格式，避免从长文本中截取无效片段
• 直接提取三个独立字段，无需unnest

修改后的查询语句 #

方式1：使用regexp_matches提取字段 #

SELECT
  i."Id",
  i."Description",
  -- 提取userid
  (regexp_matches(i."Description", '^(\d+)-(\d{4}/\d{2}/\d{2})-(\d+(?:,\d+)*)-$'))[1] AS userid,
  -- 提取date
  (regexp_matches(i."Description", '^(\d+)-(\d{4}/\d{2}/\d{2})-(\d+(?:,\d+)*)-$'))[2] AS date,
  -- 提取amount
  (regexp_matches(i."Description", '^(\d+)-(\d{4}/\d{2}/\d{2})-(\d+(?:,\d+)*)-$'))[3] AS amount
FROM "public"."Invoices" i
-- 过滤仅符合格式的记录
WHERE regexp_matches(i."Description", '^(\d+)-(\d{4}/\d{2}/\d{2})-(\d+(?:,\d+)*)-$') IS NOT NULL

方式2：使用substring更高效提取 #

SELECT
  i."Id",
  i."Description",
  substring(i."Description" FROM '^(\d+)-') AS userid,
  substring(i."Description" FROM '-(\d{4}/\d{2}/\d{2})-') AS date,
  substring(i."Description" FROM '-(\d+(?:,\d+)*)-$') AS amount
FROM "public"."Invoices" i
-- 过滤仅符合格式的记录
WHERE i."Description" ~ '^(\d+)-(\d{4}/\d{2}/\d{2})-(\d+(?:,\d+)*)-$'

原查询的问题说明 #

原正则(\d+)-(.*?)-(.*)-过于宽松：

• .*?和.*会匹配任意字符（包括空格、文字），导致从类似Tracking code: ...的长文本中错误截取片段
• unnest会把匹配结果拆分为多行，不符合你需要三个独立字段的需求

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