PostgreSQL中字符串添加小数点结果异常，求正确SQL实现方案

问题场景 #

数据表table（注意：table是PostgreSQL关键字，建议改用其他表名或用双引号包裹"table"）的col1列数据如下：

col1 
A018
B672

需要将col1处理为倒数第一个字符前添加小数点的格式，预期输出：

col1   col2
A018   A01.8
B672   B67.2

原SQL的问题 #

你使用的正则表达式'(.{2})(?!$)'逻辑错误：它会匹配每两个连续字符（只要后面不是字符串结尾），并在其后添加小数点，导致替换结果不符合预期。

正确解法 #

方法1：使用字符串截取函数（推荐，性能更优） #

通过left()截取除最后一个字符外的部分，right()截取最后一个字符，再用||拼接小数点：

WITH missed_codes AS (
    SELECT 
        col1, 
        left(col1, length(col1) - 1) || '.' || right(col1, 1) AS col2
    FROM "table"  -- 用双引号包裹关键字table避免语法错误
)
SELECT * FROM missed_codes;

方法2：使用正则表达式替换 #

匹配所有字符直到倒数第一个位置，再拼接小数点和最后一个字符：

WITH missed_codes AS (
    SELECT 
        col1, 
        regexp_replace(col1, '(.*)(.)$', '\1.\2') AS col2
    FROM "table"
)
SELECT * FROM missed_codes;

两种方法都能得到预期的输出结果。

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