PostgreSQL：如何将软件版本字符串转换为带前导零的整数

核心思路是拆分版本号各段、补全前导零后再拼接，具体步骤和SQL实现如下：

• 拆分版本号：用string_to_array(app_version, '.')将版本号按.分割成字符串数组，比如1.1.0会转为['1','1','0']。
• 补前导零：对数组中的每个元素，用lpad(elem, 2, '0')将其补全为两位字符，不足两位的前面补0，比如'1'变'01'，'18'保持'18'。
• 拼接结果：将处理后的数组元素直接拼接成完整字符串。

1. 查询转换结果的SQL #

SELECT 
  app_version,
  array_to_string(
    array(SELECT lpad(unnest(string_to_array(app_version, '.')), 2, '0')),
    ''
  ) AS app_version_converted
FROM your_table_name;

或者用更简洁的string_agg写法：

SELECT 
  app_version,
  string_agg(lpad(version_part, 2, '0'), '') AS app_version_converted
FROM your_table_name,
     unnest(string_to_array(app_version, '.')) AS version_part
GROUP BY app_version;

2. 新增并填充转换列 #

如果需要将转换结果持久化到表中：

-- 新增列（三段版本号对应6位长度，多段可调整）
ALTER TABLE your_table_name ADD COLUMN app_version_converted VARCHAR(6);

-- 填充转换后的值
UPDATE your_table_name
SET app_version_converted = array_to_string(
    array(SELECT lpad(unnest(string_to_array(app_version, '.')), 2, '0')),
    ''
  );

注意事项 #

• 如果版本号存在多段（比如1.2.3.4），上述方法依然有效，会自动将每段补两位后拼接。
• 如果版本号包含非数字字符，需要先做格式校验或清洗，比如用regexp_match过滤出合法的数字段版本号。

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