PostgreSQL中从测量范围字段提取最小/最大值并生成新列

针对你需要从range字段拆分出low（区间最小值）和high（区间最大值），且缺失端设为NULL的需求，下面分不同数据库给出具体实现方案，解决正则提取失败的问题：

核心匹配逻辑 #

先明确range字段的常见格式及对应规则：

• 完整区间（如10-20、-15--5）：提取两端数字分别作为low和high
• 左开放区间（如<5、-25）：low为NULL，提取右侧数字作为high
• 右开放区间（如>30、15-）：提取左侧数字作为low，high为NULL

PostgreSQL 实现 #

使用regexp_match函数结合正则分组提取，同时用CASE判断区间类型：

SELECT
  range,
  -- 提取low值：匹配开头的数字（含负号、小数），无则返回NULL
  CASE
    WHEN range ~ '^-?\d+(\.\d+)?' THEN (regexp_match(range, '^(-?\d+(\.\d+)?)'))[1]::numeric
    ELSE NULL
  END AS low,
  -- 提取high值：匹配结尾的数字（含负号、小数），无则返回NULL
  CASE
    WHEN range ~ '\d+(\.\d+)?$' THEN (regexp_match(range, '(-?\d+(\.\d+)?)$'))[1]::numeric
    ELSE NULL
  END AS high
FROM your_table;

说明：

• ~操作符用于正则匹配，判断是否存在符合条件的数字
• regexp_match返回匹配的分组结果，取第一个分组转换为数值类型
• 正则-?\d+(\.\d+)?支持整数、小数及负数值

MySQL 实现 #

使用REGEXP_SUBSTR提取匹配内容，搭配CAST转换数值类型：

SELECT
  range,
  CASE
    WHEN range REGEXP '^-?[0-9]+(\.[0-9]+)?' THEN CAST(REGEXP_SUBSTR(range, '^-?[0-9]+(\.[0-9]+)?') AS DECIMAL(10,2))
    ELSE NULL
  END AS low,
  CASE
    WHEN range REGEXP '[0-9]+(\.[0-9]+)?$' THEN CAST(REGEXP_SUBSTR(range, '[0-9]+(\.[0-9]+)?$') AS DECIMAL(10,2))
    ELSE NULL
  END AS high
FROM your_table;

说明：

• REGEXP_SUBSTR直接返回匹配的字符串片段
• DECIMAL(10,2)可根据实际数据精度调整

SQL Server 实现 #

用PATINDEX定位匹配位置，结合SUBSTRING提取内容：

SELECT
  range,
  CASE
    WHEN PATINDEX('^-?[0-9]%', range) > 0 THEN 
      CAST(SUBSTRING(range, PATINDEX('^-?[0-9]%', range), 
        CASE WHEN CHARINDEX('-', range) > 0 THEN CHARINDEX('-', range) - PATINDEX('^-?[0-9]%', range) ELSE LEN(range) END) 
      AS NUMERIC(10,2))
    ELSE NULL
  END AS low,
  CASE
    WHEN PATINDEX('%[0-9]$', range) > 0 THEN 
      CAST(SUBSTRING(range, 
        CASE WHEN CHARINDEX('-', range) > 0 THEN CHARINDEX('-', range) + 1 ELSE 1 END, 
        LEN(range) - CASE WHEN CHARINDEX('-', range) > 0 THEN CHARINDEX('-', range) ELSE 0 END) 
      AS NUMERIC(10,2))
    ELSE NULL
  END AS high
FROM your_table;

说明：

• PATINDEX返回匹配模式的起始位置
• 通过CHARINDEX定位分隔符-的位置，拆分左右两段内容

测试验证 #

以下是常见场景的转换结果：

如果你的range字段包含单位（如10mg-20mg），只需在正则中追加单位匹配规则（如-?\d+(\.\d+)?mg）即可调整提取逻辑。

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