PostgreSQL正则替换：去除十六进制数前导零且保留全零值

解决方案1：单正则表达式处理 #

可以通过嵌套的regexp_replace结合负向前瞻断言，一次完成前缀移除和前导零清理：

SELECT regexp_replace(regexp_replace(col1, '''h', ''), '^0+(?!$)', '') AS normalized_hex
FROM foo;

正则逻辑说明：

• 第一步regexp_replace(col1, '''h', '')：移除十六进制字符串的'h前缀。
• 第二步regexp_replace(..., '^0+(?!$)', '')：
  • ^0+：匹配字符串开头的一个或多个零。
  • (?!$)：负向前瞻断言，确保匹配的零后面不是字符串结尾。
  • 实际效果：
    • 全零字符串（如000000000）：仅最后一个零不满足“后面非结尾”，前面的所有零被替换为空，保留最后一个0。
    • 带前导零的非零字符串（如000123abc）：开头的所有零被匹配替换，留下有效十六进制部分。
    • 无前置零的字符串（如123abc）：正则无匹配，直接返回原字符串。

测试结果 #

执行上述SQL后，输出结果符合预期：

normalized_hex
----------------
 123abc
 123abc
 123abc
 0
(4 rows)

解决方案2：CASE表达式（更易读） #

如果觉得正则断言不够直观，可用CASE分支处理，逻辑更清晰：

SELECT 
  CASE 
    WHEN trimmed = '' THEN ''
    WHEN trimmed ~ '^0+$' THEN '0'
    ELSE regexp_replace(trimmed, '^0+', '')
  END AS normalized_hex
FROM (
  -- 先统一移除前缀
  SELECT regexp_replace(col1, '''h', '') AS trimmed
  FROM foo
) t;

逻辑说明：

1. 先移除'h前缀得到trimmed字符串。
2. 判断如果trimmed全是零（trimmed ~ '^0+$'），则返回单个0。
3. 否则移除开头的所有前导零。

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