SQL数值列存储子串问题：前导零丢失的排查与解决

首先得明确一个核心关键点：numeric这类数值类型本身并不存储前导零。因为098和98在数值逻辑上完全等价，当你把字符串'098'转换为numeric类型时，数据库会自动忽略前导零，只保留实际的数值大小——这就是行3的val最终是98而非预期098的根本原因。

根据你的实际需求，这里有两种可行的解决方向：

方向1：保留前导零格式存储 #

如果你的核心诉求是完整存储带前导零的数字格式，那val字段就不适合用数值类型，应该改用字符类型（比如varchar(3)或char(3)）。具体操作如下：

1. 修改表结构，将val字段转为字符类型：

ALTER TABLE tab1 ALTER COLUMN val TYPE varchar(3);

2. 更新数据时直接存入提取到的数字子串，无需转换为数值：

UPDATE tab1 
SET val = substring(trim(tmp), 4, 3) 
WHERE substring(trim(tmp), 4, 3) ~ '^\s*[0-9]+$';

这样行3的val就会被存储为'098'，完全保留原字符串的格式。

方向2：数值类型存储，查询时补全前导零 #

如果必须用numeric类型存储数值（比如需要基于该字段做数值运算），那可以在查询阶段通过函数补全前导零，实现显示098的效果。PostgreSQL的lpad()函数可以轻松完成这个需求：

SELECT 
    tmp, 
    val,
    lpad(val::varchar, 3, '0') AS formatted_val  -- 将数值转成字符串后补零到3位
FROM tab1;

这个查询会返回formatted_val列，其中行3的内容就是'098'，而val字段仍然存储纯数值98，不会影响后续的数值运算操作。

优化你的UPDATE语句 #

另外，你的原UPDATE语句重复调用了substring(trim(tmp), 4, 3)，可以用CTE（公共表表达式）简化逻辑，提升可读性和执行效率：

WITH num_substrings AS (
    SELECT 
        tmp,
        substring(trim(tmp), 4, 3) AS num_str
    FROM tab1
    WHERE substring(trim(tmp), 4, 3) ~ '^\s*[0-9]+$'
)
UPDATE tab1
SET val = CAST(num_substrings.num_str AS NUMERIC)
FROM num_substrings
WHERE tab1.tmp = num_substrings.tmp;

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