字符函数的第一批——SUBSTR、INSTR、REPLACE、LPAD、TRIM——多数人半天就能摸熟，因为它们的行为符合直觉：给位置、给长度、给替换串，结果一眼能对上。真正把线上数据搅乱、把数据库 CPU 拉到告警线、把导出文件变成乱码的，往往是第二批：TRANSLATE、正则四件套、LISTAGG，以及那几个名字看起来人畜无害的LENGTHB、SUBSTRB、DUMP。这篇就专门讲这一批字符型函数，不谈语法手册上抄一遍就有的东西，重点放在“它到底怎么算”“什么场景该用它”“用错了会出什么事”。

适合的读者很明确：已经能写日常SELECT，但在做数据清洗、报表拼接、日志解析、老系统数据迁移时被字符问题绊过一跤的人。文中的每个例子都能直接在dual上跑出来，每个坑都是可以复现的，不是纸上谈兵。我会把参数的含义、边界条件、性能代价一起讲清楚，你照着改自己的 SQL 就行。

1. 第二梯队字符函数的共同特征：它们都在“猜”你的意图

第一批函数是确定性的位置运算，第二批函数大多带有“模式”或“映射”的色彩。TRANSLATE要你给出逐字符的对应关系，正则要你描述一个模式，LISTAGG要你声明排序规则，LENGTHB则要求你知道底层字符集是什么。只要有一处没对齐，函数不会报错，它会安安静静给你一个看起来“差不多”的结果。

1.1 静默错误比报错更贵

我见过最典型的一次事故，是一个金额字段在清洗时用了TRANSLATE(amount_str, ',', '')，本意是去掉千分位逗号。SQL 跑完不报错，导出的表里整列全是空值。原因很简单：to_string传了空字符串，而 Oracle 里空字符串等于NULL，TRANSLATE只要任一参数为NULL，结果就是NULL。这不是函数写错了，是对“空即 NULL”这条底层规则没有敬畏。

这类问题在正则上同样存在。REGEXP_REPLACE(col, '[0-9]', '')本意是剔除数字，如果某行数据全是数字，返回的“空字符串”在 Oracle 里就是NULL，接着你在外层做LENGTH()判断、做NVL()拼接，逻辑就全歪了。所以第二批函数的第一条纪律是：每次写完，先拿边界数据（全匹配、零匹配、含 NULL、含多字节字符）跑一遍，看返回值是空串还是 NULL。

1.2 三类典型使用场景，代价差别很大

同样调这些函数，放在不同位置，代价可以差一个数量级。我的经验是可以粗分成三类：

WHERE里用正则是最容易被低估的。开发环境几万行数据感觉不出来，生产上千万行一跑，CPU time直接翻倍，执行计划从INDEX RANGE SCAN变成TABLE ACCESS FULL。这不是正则本身的错，是它天生无法用 B-Tree 索引做范围定位——索引存的是原值，正则要的是“符合模式的任意值”，两者对不上。

2. TRANSLATE 的逐字符映射：一次不报错的批量改写

TRANSLATE(expr, from_string, to_string)的语义只有一句话：把expr里出现在from_string中的每个字符，替换成to_string中相同位置的字符。注意是“字符”，不是“字符串”，这是它和REPLACE最本质的区别。REPLACE找的是一段连续的串，TRANSLATE找的是单个字符的集合。

2.1 位置对应关系决定了三种行为

理解TRANSLATE只需要盯住三件事：

• to_string比from_string短：多出来的那些源字符被删除。
• to_string比from_string长：多出来的目标字符被忽略。
• from_string里有重复字符：实际生效的是哪一个，官方文档没有明确承诺，不要依赖。

第二条经常被忽略。有人写TRANSLATE(code, 'AB', 'XYZ')，以为A→X、B→Y，Z是备用，实际Z永远用不上。第三条更要命，如果from_string里同一个字符出现两次、映射到两个不同目标，结果就是不确定的，跨版本升级可能变。

2.2 删除字符的正确写法：哨兵字符法

想用TRANSLATE删掉某几个字符，不能把to_string留空。正确做法是引入一个原串里绝不会出现的“哨兵字符”，让它在映射表里首尾呼应：

-- 目标：删掉连字符，2024-05-01 变成 20240501 SELECT TRANSLATE('2024-05-01', '~-', '~') FROM dual; -- 结果：20240501 -- 错误写法：to_string 为空，整体返回 NULL SELECT TRANSLATE('2024-05-01', '-', '') FROM dual; -- 结果：NULL

~就是哨兵。from_string是~-，to_string是~，于是~映射到自己（等于没变），-没有对应目标，被删除。哨兵的选择标准很简单：确认它不在源数据里出现。如果是纯数字清洗用~，是英文文本可以用|或者^，实在不放心就先用INSTR探一下。

2.3 白名单式数字清洗：把脏金额拉回规范

真实业务里的金额字段经常是这个样子：￥1,234.56元、1 234.56、(1,234.56)表示负数。用TRANSLATE做白名单清洗，比一堆REPLACE嵌套干净得多：

WITH t AS ( SELECT '￥1,234.56元' AS raw FROM dual UNION ALL SELECT '1 234.56' FROM dual UNION ALL SELECT '-1,234.56' FROM dual ) SELECT raw, TRANSLATE(raw, '0123456789.-' || '￥,元 ', '0123456789.-') AS cleaned FROM t;

思路是把from_string拆成两段：前一段是“要保留的字符集”，to_string与它完全一致，等于自身映射；后一段是“要删除的字符集”，在to_string里没有位置，自动删除。结果是1234.56、1234.56、-1234.56。

这里有个必须点明的局限：白名单只能穷举你想到的字符。如果数据里冒出一个$或者一个全角逗号，它不在from_string里，就会原样透传，TO_NUMBER照样报ORA-01722。所以我在生产脚本里的做法是两步走：先TRANSLATE清洗已知字符，再用REGEXP_REPLACE兜底剔除一切非[0-9.\-]的残留，最后TO_NUMBER。多一步，但半年内没再被脏数据叫起来过。

2.4 CLOB 与多字节字符的两个边界

TRANSLATE对CLOB不是原生支持，文档明确写的是“不直接支持 CLOB，但可以通过隐式转换传入”。隐式转换的潜台词是：超过 4000 字节的 CLOB 会被截断或者直接抛错，取决于具体路径。要处理大文本，老老实实用DBMS_LOB.SUBSTR分段取，或者改用REGEXP_REPLACE（正则函数是原生支持 CLOB 的）。

多字节字符方面，TRANSLATE是按字符处理的，中文没有问题，TRANSLATE('中文abc', 'abc', 'xyz')会得到中文xyz。但如果数据库字符集是AL32UTF8、而你的from_string里混了一个四字节的表情符号，映射依然成立，只是可读性差到没人愿意维护。我的建议是：涉及非 ASCII 字符的映射，一律在注释里写清楚每个字符的 Unicode 码点，否则三个月后自己都看不懂。

3. 正则四件套的分工：别拿锤子当螺丝刀

REGEXP_LIKE、REGEXP_SUBSTR、REGEXP_REPLACE、REGEXP_INSTR、REGEXP_COUNT这五个函数，参数列表高度相似（源串、模式、起始位置、出现次数、匹配模式），但用途完全不同。用错工具的代价不是结果错，而是慢，慢到让人误以为是数据库性能问题。

3.1 REGEXP_SUBSTR 的 occurrence 与 subexpr

REGEXP_SUBSTR(source, pattern, position, occurrence, match_param, subexpr)里最有用的是后两个参数。occurrence指定取第几次匹配，subexpr指定取第几个捕获组（0是整段，1起是括号内的组）。

-- 取邮箱域名部分 SELECT REGEXP_SUBSTR('zhang.san@example.org', '@([^.]+)', 1, 1, NULL, 1) FROM dual; -- 结果：example -- 取第二段数字 SELECT REGEXP_SUBSTR('a1b22c333', '[0-9]+', 1, 2) FROM dual; -- 结果：22

很多人的痛点是“我要取最后一个分隔符之后的片段”，比如文件路径取文件名。正则里可以用贪婪匹配加否定字符类：REGEXP_SUBSTR(path, '[^/]+$')。注意$默认匹配整个串的末尾，如果字符串里含换行，需要加'n'参数让.匹配换行，或者用'm'让^、$按行匹配。这两个参数搞混是排查半天都找不到原因的重灾区。

3.2 REGEXP_REPLACE 的回引与 ordering

手机号格式化是最能说明回引价值的例子：

SELECT REGEXP_REPLACE('13812345678', '([0-9]{3})([0-9]{4})([0-9]{4})', '\1-\2-\3') FROM dual; -- 结果：138-1234-5678

\1、\2引用的是模式里的第几个捕获组。这里有两个坑。第一，如果你的 SQL 是从 Java 或者 Python 代码里拼出来的，反斜杠可能被上层语言先吃掉一次，最后传进数据库的变成了1-2-3，结果就是原样输出。第二，如果替换串里需要真正的反斜杠字符，得写成\\。我在代码里看到REGEXP_REPLACE一律先看一眼转义层数。

另外REGEXP_REPLACE默认替换全部匹配（occurrence默认 0），这点和很多语言里需要加g标志不同，是 Oracle 的特性，不是遗漏。

3.3 match_param 的四个取值要背下来

'm'和'n'的区别值得单独说一句：'m'改变的是锚点的行为，'n'改变的是点号的行为。清洗带换行的日志文本时，用'.*'提取整段是拿不到跨行内容的，必须'n'。

3.4 正则的性能边界与替代方案

REGEXP_LIKE放在WHERE里，本质上是对每一行做一次模式匹配，无法走普通索引。数据量到百万级，一次全表扫下来几秒钟很正常，而且会吃掉大量CPU。我一般按这个顺序找替代：

1. 如果只是判断“以某串开头”，用LIKE 'ABC%'，还能走索引。
2. 如果是判断“包含某串”，用INSTR(col, 'x') > 0，比正则快明显一截。
3. 如果必须用正则且查询频繁，建函数索引CREATE INDEX idx ON t(REGEXP_SUBSTR(col, ...))，但要求查询里写完全一致的表达式，少一个空格都不走索引。
4. 如果数据是定期清洗的，干脆加一个标记列，用批处理更新，查询时只查标记列。

提示：正则里的(.*)配长字符串是性能杀手，回溯会让单行耗时从微秒级跳到毫秒级甚至更高。能用确定字符类[^,]*就不要用.*。

4. 字节与字符的错位：LENGTHB 让“长度”变成两件事

LENGTH返回字符数，LENGTHB返回字节数，VSIZE返回内部表示的字节数。三个函数，三种口径，选错一个就可能写出ORA-12899: value too large for column。

4.1 AL32UTF8 下的一笔账

数据库字符集是AL32UTF8时，一个汉字占 3 字节，一个 ASCII 字符占 1 字节，一个表情符号占 4 字节。所以：

SELECT LENGTH('中文abc') AS chars, -- 5 LENGTHB('中文abc') AS bytes, -- 3+3+1+1+1 = 9 VSIZE('中文abc') AS vsize -- 9 FROM dual;

LENGTH和LENGTHB在纯英文数据上碰巧相等，这就是为什么很多人在测试环境验证通过的脚本，一上生产就报错——测试数据全是英文。VSIZE与LENGTHB在VARCHAR2上通常一致，但数据类型换成NUMBER、DATE时，VSIZE返回的是内部存储字节，LENGTH会先隐式转成字符串，两者完全不是一回事，别混用。

4.2 VARCHAR2(50) 到底是 50 字节还是 50 字符

默认是字节。声明VARCHAR2(50)等同于VARCHAR2(50 BYTE)，能存 16 个汉字加 2 个英文字符就满了。要按字符算，必须显式写VARCHAR2(50 CHAR)。这个默认行为由初始化参数NLS_LENGTH_SEMANTICS控制，默认值就是BYTE。

我的建议很明确：新建表里所有可能存中文的列，一律写CHAR语义。原因不是省事，而是避免“长度校验通过、插入时报错”这种最难查的问题。应用层按字符数校验、数据库按字节限制，两边对不上，测试又用了英文数据，这个 bug 能潜伏到上线后第一批中文数据进来。

代价是CHAR语义下索引和存储的字节上限变大：VARCHAR2(50 CHAR)在AL32UTF8下最坏要预留 200 字节（4 字节字符场景）。对绝大多数业务表来说，这点空间可以忽略，但如果是超宽表加大量索引，还是得算一算。

4.3 SUBSTRB 截断出的半个字符

SUBSTRB按字节截取，遇上多字节字符时可能截在字符中间。比如：

SELECT SUBSTRB('中文测试', 1, 4) FROM dual;

前 3 字节是完整的“中”，第 4 字节只是“文”的第一个字节。返回的字符串里含无效字节序列，展示时通常表现为一个替换字符或者方块，导出到文件后更容易出现整段错乱。

正确做法是用SUBSTR（按字符）配合LENGTH校验，或者在必须按字节限制的场景下，先算出安全的字符数：FLOOR(字节上限 / 最大字符字节数)。如果数据里既有中文又有英文，更稳妥的方式是循环取字符直到累计LENGTHB超限，虽然啰嗦，但绝不会切坏。批量导出定长文件的场景，我一般直接用RPAD(SUBSTR(...), n)组合，靠字符语义保证不出半个字。

5. LISTAGG：最爽的拼接函数，也是最容易爆的那个

LISTAGG把多行值拼成一个字符串，配合WITHIN GROUP (ORDER BY ...)使用，语法清爽，效果直观。它在 11gR2 引入之后迅速取代了各种SYS_CONNECT_BY_PATH的奇技淫巧，但它的长度限制也制造了无数张工单。

5.1 基本形态与排序位置

SELECT dept_id, LISTAGG(emp_name, ',') WITHIN GROUP (ORDER BY emp_name) AS names FROM employees GROUP BY dept_id;

注意ORDER BY的位置：它不在LISTAGG的括号里，而在WITHIN GROUP后面。这一点和普通聚合函数手写顺序不同，很多人第一次写会放到外面去，然后收到语法错误。排序规则只影响拼接顺序，不影响分组。

5.2 ORA-01489 的根因与三条出路

12.2 之前，LISTAGG的返回值上限是VARCHAR2的 4000 字节。一旦某个分组的拼接结果超限，直接抛ORA-01489: result of string concatenation is too long。这个报错最阴的地方是：它只在数据量足够大的时候出现，开发和测试环境永远碰不到，上线三个月后某个部门的记录攒够了才爆。

三条出路，按推荐顺序：

第一条，升级到 12.2 及以上，用ON OVERFLOW TRUNCATE：

SELECT dept_id, LISTAGG(emp_name, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP (ORDER BY emp_name) AS names FROM employees GROUP BY dept_id;

WITH COUNT会在截断处补上...(25)这样的计数，告诉你还有多少条没拼进去。加不加WITH COUNT取决于业务能否接受“信息有损但可见”。这个方案的好处是 SQL 不用改结构，坏处是截断后的数据不再适合做二次解析。

第二条，用XMLAGG走 CLOB 路线：

SELECT dept_id, RTRIM( EXTRACT( XMLAGG(XMLELEMENT(E, emp_name || ',') ORDER BY emp_name), '//text()' ).getClobVal(), ',' ) AS names FROM employees GROUP BY dept_id;

XMLAGG返回XMLType，用getClobVal()转成 CLOB，不受 4000 字节限制。代价是特殊字符会被 XML 实体转义——名字里带&、<、>的会变成&之类，取出来还得反向替换，非常别扭。而且EXTRACT的//text()在不同版本上行为略有差异，需要实测。

第三条，应用层拼接。分组查出来，在代码里join。听起来原始，但对超大分组最稳，而且不受数据库版本限制。我在处理十几万条明细聚合成一个 JSON 字段的场景里，最后就是走的这条路——数据库只负责取数，拼接交给程序，内存和长度都可控。

5.3 去重拼接与 19c 的 DISTINCT

老版本里LISTAGG不支持DISTINCT，想去重拼接只能先套一层子查询：

SELECT dept_id, LISTAGG(emp_name, ',') WITHIN GROUP (ORDER BY emp_name) AS names FROM (SELECT DISTINCT dept_id, emp_name FROM employees) GROUP BY dept_id;

19c 开始可以直接写LISTAGG(DISTINCT emp_name, ',')，省一层子查询。但要注意，加了DISTINCT之后排序开销会上去，因为需要先去重再排序。数据量大的分组，子查询版本反而可能更快，因为去重可以在扫描阶段就完成。

还有个老生常谈的问题：12c 之后WM_CONCAT已经被移除，任何还留着它的老脚本升级时都会报ORA-00904: invalid identifier。迁移时直接换成LISTAGG，同时检查拼接结果长度，别只改函数名。

注意：LISTAGG的结果长度是按字节算的，不是按字符。一组中文名字拼起来，字符数看着没超，字节数早就过 4000 了。

6. 编码排查工具：DUMP、ASCIISTR、UNISTR 三件套

乱码问题最忌讳猜。猜字符集、猜编码、猜是哪里截断的，往往越猜越乱。DUMP、ASCIISTR、UNISTR这三个函数的价值就在于把“看不见的东西”变成“看得见的东西”。

6.1 DUMP 看字节，ASCIISTR 看转义

SELECT DUMP('中', 16) FROM dual; -- Typ=1 Len=3: e4,b8,ad

Typ=1表示这是字符串类型，Len=3是字节长度，后面是每个字节的十六进制。AL32UTF8下“中”的 UTF-8 编码是E4 B8 AD，对得上就说明存储层没问题，乱码出在客户端或者导出环节。

ASCIISTR更适合看整体：

SELECT ASCIISTR('中a') FROM dual; -- \4E2D a

它把非 ASCII 字符转成\XXXX形式的 Unicode 码点，ASCII 字符原样保留。这个输出很好读，用来确认“这个字符串里到底有几个字符、分别是什么码点”非常方便。反向操作是UNISTR，把\XXXX还原成字符：UNISTR('\4E2D')得到“中”。构造那些“看不见的字符”时特别有用，比如全角空格UNISTR('\3000')。

6.2 清洗不可见字符的两种写法

从外部系统导入的数据，最常见的脏东西是制表符、回车、换行、不换行空格（U+00A0）和全角空格。前三个可以用TRANSLATE一次搞定：

SELECT TRANSLATE(col, CHR(9) || CHR(10) || CHR(13), ' ') FROM t;

to_string给了三个空格，所以三个控制字符各自映射成一个空格，位置对应上了，不会误删。如果想让它们直接消失，把to_string换成哨兵方案即可。

后两个更麻烦，因为它们肉眼看起来和普通空格一样。最稳的写法是用REGEXP_REPLACE加码点范围：

SELECT REGEXP_REPLACE(col, '[' || UNISTR('\00A0') || UNISTR('\3000') || ']', ' ') FROM t;

或者更粗暴一点，用[[:cntrl:]]剔掉所有控制字符：REGEXP_REPLACE(col, '[[:cntrl:]]', '')。注意这个写法会连带删掉换行，多行文本要谨慎。

6.3 COMPOSE 与 DECOMPOSE：长得一样但不相等

带音标的拉丁字母有两种表示法：一种是单个码点（组合形式），一种是基字母加组合音标（分解形式）。两者显示出来一模一样，但=比较和LIKE匹配都会认为不相等。用户从不同输入法粘贴数据时，这两种形式可能混在一起，导致“明明有这条记录却查不到”。

SELECT COMPOSE('e' || UNISTR('\0301')) FROM dual; -- é（组合形式） SELECT DECOMPOSE('é') FROM dual; -- e + ́ （分解形式）

处理办法是入库前统一做一次COMPOSE，或者在比较时两边都套COMPOSE。中文数据不受影响，但涉及多语言用户名的表，这一条能省掉大量莫名其妙的重复记录排查。

7. 排序与比较里的字符函数：大小写和语言的坑

UPPER、LOWER、INITCAP是第一批的老面孔，但它们在排序和索引场景下的表现，属于第二批需要掌握的深度内容。

7.1 UPPER 加函数索引，查询必须完全对齐

最常见的需求是“用户名不区分大小写查询”。直觉写法：

SELECT * FROM users WHERE UPPER(user_name) = UPPER(:input);

这条 SQL 不会走user_name上的普通索引，因为索引存的是原值。解决办法是建函数索引：

CREATE INDEX idx_users_upper_name ON users(UPPER(user_name));

建完之后，查询必须写完全相同的表达式才能命中。UPPER(TRIM(user_name))、UPPER(user_name || '')、大小写不一致的函数名，全部不走。这个坑我踩过：索引明明建了，执行计划还是全表扫，查了二十分钟才发现是查询里多套了一层TRIM。后来改成索引和查询都统一用UPPER(TRIM(user_name))，才算对上。

还有一点，函数索引要求函数是确定性的。TO_CHAR(SYSDATE, ...)这种依赖运行环境的表达式不能进函数索引，而UPPER、LOWER可以。至于NLS_UPPER、NLS_LOWER、NLS_INITCAP这些带语言环境的版本，行为会受会话的NLS_SORT影响，用作索引表达式时得格外小心，最好显式指定参数。

7.2 中文排序：NLSSORT 才是正解

ORDER BY name排中文，默认按二进制编码排，得到的顺序既不是拼音也不是笔画，用户看了会以为数据是乱的。按拼音排序要借助NLSSORT：

SELECT name FROM users ORDER BY NLSSORT(name, 'NLS_SORT=SCHINESE_PINYIN_M');

_M后缀表示“多语言排序规则”，不能省。如果整张表经常按这个规则排序，可以建对应的函数索引，让排序变成索引扫描：

CREATE INDEX idx_users_name_pinyin ON users(NLSSORT(name, 'NLS_SORT=SCHINESE_PINYIN_M'));

代价是索引体积会变大，而且这个索引只对这一种排序规则有效。如果业务同时需要拼音序和笔画序，就得建两个索引，写之前先想清楚值不值。

7.3 SOUNDEX 的实际用途很有限

SOUNDEX返回一个四字符编码，表示英文单词的发音。它只对英文有效，对中文完全无意义，对带数字和符号的字符串也基本没用。它真正能用的场景非常窄：英文人名的模糊匹配。而且在中文为主的数据里，用UTL_MATCH包里的相似度函数往往效果更好。我的建议是，除非明确知道数据是纯英文姓名，否则不要引入SOUNDEX，它带来的困惑通常大于收益。

8. 把这些函数写进生产代码前的几条硬规矩

讲完具体函数，说几条我在实际项目里总结的规矩。这些不是语法要求，但能挡掉大部分线上问题。

8.1 边界数据集先行

任何涉及字符清洗、正则匹配、拼接的 SQL，上线前必须准备一份边界数据集跑一遍，至少包含：全匹配的行、零匹配的行、NULL值、纯中文、中英混排、含表情符号、超长字符串（接近 4000 字节）、含控制字符。这份数据集建议固化成一张测试表，长期留着，每次改 SQL 都跑一遍。听起来费事，但比事后从备份里捞数据便宜太多。

8.2 记住“空串即 NULL”这条底层规则

这一条值得单独拎出来。Oracle 里''就是NULL，所以任何可能返回空串的字符串函数，返回值都可能是NULL。SUBSTR、REGEXP_REPLACE、TRANSLATE、TRIM全都在这个范围内。写外层逻辑时，要么用NVL兜底，要么在应用层判空，别指望它返回一个长度为 0 的字符串。

8.3 常用函数速查表

8.4 一个我常备的清洗函数

最后分享一个我几乎每个项目都会建的辅助函数，把上面几招串起来：先剔除控制字符，再统一全角空格，最后压缩连续空格。

CREATE OR REPLACE FUNCTION clean_text(p_str IN VARCHAR2) RETURN VARCHAR2 IS v_str VARCHAR2(32767) := p_str; BEGIN IF v_str IS NULL THEN RETURN NULL; END IF; -- 控制字符统一成空格 v_str := TRANSLATE(v_str, CHR(9) || CHR(10) || CHR(13), ' '); -- 不换行空格与全角空格统一成半角 v_str := REGEXP_REPLACE(v_str, '[' || UNISTR('\00A0') || UNISTR('\3000') || ']', ' '); -- 连续空格压缩为一个 v_str := REGEXP_REPLACE(v_str, ' {2,}', ' '); RETURN TRIM(v_str); END; /

调用一次，后面所有判断和比较都基于干净数据，能省掉大量“为什么这条明明一样却匹配不上”的排查时间。函数里故意用了VARCHAR2(32767)而不是VARCHAR2(4000)，是为了在 PL/SQL 环境里能处理更长的入参；如果入参来自表列，注意列定义本身不能超过 4000 字节（除非用扩展数据类型）。

踩过几次坑之后我的体会是：这批字符函数真正的难点从来不在语法，而在于你是否清楚数据长什么样、字符集是什么、长度按什么口径算。把这三件事摸清，剩下的就是把参数填对。