SQL字段中替换前导零为下划线的实现方法求助

针对你只替换前导零的需求，不能直接用普通REPLACE（会替换所有零），下面分几种常用数据库给出具体实现：

MySQL（8.0+版本） #

利用正则替换函数REGEXP_REPLACE，结合REPEAT生成对应长度的下划线：

SELECT 
  your_column,
  REGEXP_REPLACE(your_column, '^0+', REPEAT('_', CHAR_LENGTH(REGEXP_SUBSTR(your_column, '^0+')))) AS replaced_column
FROM your_table;

如果字段全是零（比如'0000'），上面的语句也会正确替换成'____'。

SQL Server #

用PATINDEX定位第一个非零字符的位置，再用STUFF替换前导零为对应长度的下划线：

SELECT 
  your_column,
  CASE 
    WHEN your_column NOT LIKE '%[^0]%' THEN REPLICATE('_', LEN(your_column))
    ELSE STUFF(your_column, 1, PATINDEX('%[^0]%', your_column) - 1, REPLICATE('_', PATINDEX('%[^0]%', your_column) - 1))
  END AS replaced_column
FROM your_table;

CASE分支用来处理全零的特殊情况。

PostgreSQL #

借助正则替换和字符串重复函数：

SELECT 
  your_column,
  REGEXP_REPLACE(your_column, '^0+', REPEAT('_', LENGTH(SUBSTRING(your_column FROM '^0+')))) AS replaced_column
FROM your_table;

或者用更简洁的正则替换写法（PostgreSQL支持替换时调用函数）：

SELECT 
  your_column,
  REGEXP_REPLACE(your_column, '^(0+)', REPEAT('_', LENGTH('\1'))) AS replaced_column
FROM your_table;

通用思路（适用于多数数据库） #

1. 提取字段中的前导零部分，获取其长度
2. 生成对应长度的下划线字符串
3. 将原字段的前导零部分替换为这个下划线字符串

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