在HQL中实现从右数第6位后添加小数点的正则表达式方案

需求与场景 #

需要对长度为15位的字符串，从右往左数第6位后添加小数点，保证小数点后保留6位。

原表数据（Tab1） #

Col1    Col2
---    -----
1      000000000000098
2      000000000000234
5      000000000024556
6      000000012345677
7      000000013ABC686

期望输出 #

Col1  Col2
---   ----
1     0.000098
2     0.000234
5     0.024556
6    12.345677
7    13.ABC686

原SQL方案的问题 #

标准SQL中可以通过以下代码实现（匹配前9位数字后加小数点）：

SELECT
  col,
  regexp_replace(col,'^([[:digit:]]{9})','\1.') AS res
FROM tab;

但该逻辑直接迁移到HQL后执行异常，输出结果错误地将前缀替换为1，不符合预期。

正确的HQL实现 #

HQL的regexp_replace函数在反向引用捕获组时，使用$n而非\n，同时需要兼容非数字字符的场景，最终代码如下：

SELECT
  Col1,
  regexp_replace(Col2, '^(.{9})', '$1.') AS formatted_Col2
FROM Tab1;

关键说明 #

1. 反向引用差异：HQL中必须用$1指代第一个捕获组，原SQL的\1在HQL中会被解析为字符1，这是导致错误的核心原因。
2. 正则适配性：^(.{9})匹配字符串开头的任意9个字符（支持数字、字母等），满足15位字符串从右数第6位后加小数点的需求（15-6=9），同时兼容含非数字的场景。

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