1. 项目概述：为什么“字符截取”是数据库操作中的高频刚需？

在数据库的日常运维和开发工作中，数据清洗、格式化、报表生成是绕不开的几座大山。很多时候，我们从业务系统、外部文件或API接口拿到的数据，其格式并不完全符合我们的使用要求。比如，一个存储了完整地址的字段，我们可能只需要提取其中的省份信息；一个包含了用户全名的字段，我们可能需要拆分成姓和名；又或者，一个冗长的产品编码，我们只需要截取其中代表类别的几位字符。这些场景，都指向一个核心操作：字符截取。

对于PostgreSQL（简称PG）数据库的用户而言，掌握其内置的字符串处理函数，尤其是字符截取功能，是提升数据处理效率、保证数据质量的基本功。这不仅仅是写一个SUBSTRING函数那么简单，它涉及到编码认知、性能考量以及不同场景下的最佳实践选择。网上虽然有很多零散的教程，但往往只讲语法，不讲背后的逻辑和踩坑经验。今天，我就结合自己十多年在数据领域摸爬滚打的经验，把PG数据库里关于字符截取的“里里外外”都拆解清楚，从最基础的函数使用，到多字节字符（如中文）处理的陷阱，再到结合正则表达式的高级玩法，最后分享几个真实业务场景下的优化案例。无论你是刚接触PG的开发者，还是需要经常处理数据的数据分析师，这篇文章都能让你对“截取”这个操作有全新的认识。

2. 核心函数库深度解析：不止是SUBSTRING

PG提供了丰富的字符串函数，用于截取操作的主要有SUBSTRING、LEFT/RIGHT、SPLIT_PART，以及用于按位置提取的SUBSTR（与SUBSTRING类似）。理解它们的细微差别，是高效应用的第一步。

2.1 SUBSTRING：功能最全面的“瑞士军刀”

SUBSTRING函数是PG中用于截取子字符串的核心武器，其语法灵活，支持从字符串中提取任意位置、任意长度的部分。

基本语法：

SUBSTRING(string FROM start_position [FOR length])

或者使用更常见的带括号的语法：

SUBSTRING(string, start_position [, length])

参数深度解读：

• string：需要处理的源字符串。这里有一个至关重要的细节：在PG中，字符串的索引从1开始，而不是像某些编程语言（如Python、C）那样从0开始。这是新手最容易踩的第一个坑。例如，字符串'PostgreSQL'的第一个字符是'P'，位置是1。
• start_position：开始截取的位置。它必须是正整数。如果为负数或0，在标准用法中会导致返回空字符串，除非与SIMILAR TO或正则表达式一起使用（后面会讲到）。
• length：可选参数，指定要截取的字符数。如果省略，则默认截取从start_position开始直到字符串末尾的所有字符。

实操示例与心得：假设我们有一个订单号字段，格式为'ORD-20231015-001'，我们希望提取中间的日期部分'20231015'。

SELECT SUBSTRING('ORD-20231015-001' FROM 5 FOR 8); -- 或 SELECT SUBSTRING('ORD-20231015-001', 5, 8);

注意：这里FROM 5是因为'ORD-'占了4个字符，第5个字符开始就是日期。精确计算起始位置是使用SUBSTRING的关键。在复杂的字符串中，我通常会先用POSITION或STRPOS函数找到关键分隔符（如-）的位置，再动态计算起始点，而不是硬编码数字，这样代码更健壮。

2.2 LEFT与RIGHT：从两端下手的“快捷工具”

当你明确需要从字符串的开头或结尾截取特定数量的字符时，LEFT和RIGHT函数提供了更直观、更简洁的写法。

语法：

LEFT(string, n) -- 返回字符串左边的n个字符 RIGHT(string, n) -- 返回字符串右边的n个字符

适用场景对比：

• LEFT：常用于提取固定长度的前缀，比如国家代码、固定长度的用户ID前缀等。

  SELECT LEFT('CN-北京市海淀区', 2); -- 返回 'CN'

• RIGHT：常用于提取后缀，比如文件扩展名、手机号后四位、订单号的序列部分等。

  SELECT RIGHT('document_backup.pdf', 3); -- 返回 'pdf' -- 但注意，如果文件名没有扩展名或多于一个点，这方法会出错。更可靠的是结合`SPLIT_PART`。

实操心得：LEFT和RIGHT虽然简单，但在处理不定长字符串时要格外小心。例如，用RIGHT(phone_number， 4)提取手机号后四位时，必须确保phone_number字段里所有值的长度都大于等于4，否则可能返回比预期短的结果或原字符串（如果n大于字符串长度，则返回整个字符串）。在清洗数据时，先做长度校验是一个好习惯。

2.3 SPLIT_PART：基于分隔符的“精准手术刀”

当你的字符串有明确的分隔符（如逗号、横杠、斜杠）时，SPLIT_PART是比SUBSTRING更优雅、更不易出错的选择。它直接将字符串按分隔符拆分成多个部分，然后让你取出其中的某一块。

语法：

SPLIT_PART(string, delimiter, field_num)

• delimiter：分隔符，可以是一个或多个字符。
• field_num：要返回的部分的序号，从1开始。如果序号超出实际存在的部分数，则返回空字符串。

经典应用场景：

1. 解析CSV或日志行：SPLIT_PART('192.168.1.1 - - [15/Oct/2023:10:20:30]', ' ', 1)可以提取IP地址。
2. 处理路径：SPLIT_PART('/usr/local/bin/python', '/', 4)可以提取文件名'python'（注意，开头的空字段也算）。
3. 解决上述RIGHT提取扩展名的问题：

   SELECT SPLIT_PART('archive.tar.gz', '.', -1); -- 返回 'gz'？ 错误！

   这里有个大坑：SPLIT_PART的field_num参数不支持负数索引（不像Python的列表）。要取最后一个元素，你需要知道总共有几部分。一个常用的技巧是结合STRING_TO_ARRAY和数组下标：

   SELECT (STRING_TO_ARRAY('archive.tar.gz', '.'))[array_length(STRING_TO_ARRAY('archive.tar.gz', '.'), 1)]; -- 或者使用更简洁的（PG 9.5+）： SELECT (REGEXP_SPLIT_TO_ARRAY('archive.tar.gz', '\.'))[cardinality(REGEXP_SPLIT_TO_ARRAY('archive.tar.gz', '\.'))];

函数选型速查表：

3. 进阶实战：多字节字符、正则表达式与性能陷阱

掌握了基础函数，只能算入门。在实际生产环境中，尤其是处理中文等多字节文本，或者面对复杂的、非结构化的字符串时，我们会遇到更棘手的问题。

3.1 多字节字符处理的“雷区”与解决方案

这是PG字符截取中最经典的坑。PG的SUBSTRING、LEFT、RIGHT等函数，默认操作的单位是字符（character），而不是字节（byte）。这对于单字节编码（如ASCII）是没问题的。但对于UTF-8编码的中文，一个汉字通常占3个字节，但被视为一个字符。

问题来了：有些时候，由于历史遗留问题或外部系统交互，你拿到的字符串可能是按字节长度进行限制或处理的。这时，如果你用默认的字符函数去处理，结果会完全错误。

示例：假设一个字段按字节存储，最多10字节，存入了'数据库PostgreSQL'（“数据库”各3字节，共9字节，“PostgreSQL”9字节，实际已超，这里假设）。如果你想用SUBSTRING(column， 1， 5)按字符取前5个，你会得到'数据库Po'，这看起来是5个“字符”，但字节数远不止5。

解决方案：PG提供了按字节操作的函数，通常以..._byte为后缀。

• octet_length(string)：返回字符串的字节数。
• substr(string, from_byte [, for_byte])：注意，这是substr，不是substring。这个函数可以按字节截取，但from_byte参数是从1开始的字节索引。

  SELECT substr('数据库PostgreSQL', 1, 5); -- 按字节截取，可能只截到“数据”的一部分，导致乱码 SELECT SUBSTRING('数据库PostgreSQL' FROM 1 FOR 2); -- 按字符截取，返回‘数据’

  核心要点：在涉及长度限制、存储计算或与按字节处理的系统交互时，务必明确你需要的操作单位是“字符”还是“字节”。99%的文本展示和逻辑处理场景，使用默认的按字符操作的函数（SUBSTRING）是正确的。只有在明确的字节级操作需求下，才使用substr等字节函数，并要警惕截断导致的乱码。

3.2 正则表达式：应对不规则字符串的“终极武器”

当分隔符不固定、模式复杂时，正则表达式（Regex）与SUBSTRING的结合就派上用场了。PG的SUBSTRING函数可以直接集成正则表达式。

语法：

SUBSTRING(string FROM pattern) -- 提取第一个匹配的子串 SUBSTRING(string, pattern) -- 同上 -- 或使用捕获组提取特定部分 SELECT SUBSTRING('Email: john.doe@example.com' FROM '([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2，})'); -- 返回 'john.doe@example.com'

更强大的REGEXP_MATCHES：SUBSTRING只能提取第一个匹配或第一个捕获组。REGEXP_MATCHES功能更强大，可以返回所有匹配的捕获组文本数组。

SELECT REGEXP_MATCHES('Phone: 123-456-7890, Fax: 987-654-3210', '(\d{3})-(\d{3})-(\d{4})', 'g'); -- 返回两行结果集：{123,456,7890} 和 {987,654,3210}

参数'g'表示全局匹配。

实战案例：从非标准日志中提取关键信息假设日志格式为：[ERROR][2023-10-27 15:30:01][ModuleA] Connection timeout to host 192.168.100.5我们需要提取错误级别、时间戳、模块名和IP地址。

SELECT (REGEXP_MATCHES(log_line, '^\[(ERROR|WARN|INFO)\]'))[1] as log_level, (REGEXP_MATCHES(log_line, '\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]'))[1] as log_time, (REGEXP_MATCHES(log_line, '\[([^\]]+)\]'))[3] as module, -- 取第三个[]内的内容 (REGEXP_MATCHES(log_line, 'host (\d{1，3}\.\d{1，3}\.\d{1，3}\.\d{1，3})'))[1] as host_ip FROM log_table;

注意事项：正则表达式虽然强大，但复杂度高，执行成本也高。不要在大型表的所有行上频繁执行复杂的正则匹配，这会是性能杀手。如果数据格式相对固定，应优先考虑使用SPLIT_PART或SUBSTRING与POSITION的组合。对于需要高频解析的日志，更好的做法是在数据入库前，就用日志收集器（如Logstash、Fluentd）或应用层进行解析，将结构化字段直接存入数据库。

3.3 性能考量与索引使用

字符截取函数通常会使查询无法使用现有的B树索引。因为索引存储的是原始字段的值，而SUBSTRING(column， 1， 10)是一个函数计算后的结果。

反面例子：

-- 假设在`order_id`上有索引 SELECT * FROM orders WHERE SUBSTRING(order_id FROM 5 FOR 8) = '20231015'; -- 这个查询大概率会进行全表扫描

优化方案：

1. 使用LIKE进行前缀匹配：如果查询模式固定（如总是查开头部分），且数据库是PG 9.5以上，可以尝试使用column LIKE 'pattern%'，并创建相应的text_pattern_ops索引。

   CREATE INDEX idx_orders_id_prefix ON orders (order_id text_pattern_ops); SELECT * FROM orders WHERE order_id LIKE 'ORD-20231015-%'; -- 可能用到索引

2. 表达式索引：如果你频繁地按照某种固定的截取模式进行查询，可以为这个表达式创建索引。

   CREATE INDEX idx_orders_date_part ON orders (SUBSTRING(order_id FROM 5 FOR 8)); SELECT * FROM orders WHERE SUBSTRING(order_id FROM 5 FOR 8) = '20231015'; -- 现在可以用到索引了

   代价：表达式索引会增加存储空间，并在数据插入、更新时带来额外的计算开销。只应在该查询是核心高频查询且性能瓶颈明显时使用。

3. 冗余存储：最根本的优化，是在设计表结构时，就将需要频繁查询的“部分”作为一个独立的字段存储。例如，将订单日期从order_id中提取出来，单独作为一个date类型的order_date字段。这样可以用最标准的索引获得最佳性能，也符合数据库设计范式。

4. 综合实战：从数据清洗到报表生成的完整链路

让我们通过一个模拟的真实业务场景，将上述所有知识点串联起来。

场景：我们有一个用户表users，其中full_info字段存储了杂糅的信息，格式不规则，例如：

• '张三， 13800138000， 北京市|海淀区'
• '李四|18812345678|上海市-浦东新区'
• '王五， 15600000000， 广东省广州市天河区'

目标：清洗数据，拆分成规范的表：name，phone，province，city。

步骤拆解：

4.1 第一步：探索与评估数据一致性

首先，我们需要了解分隔符的规律。通过抽样查询，发现分隔符可能是中文逗号，、竖线|或横杠-，且顺序不定。这直接排除了简单使用SPLIT_PART的可能性，必须借助正则表达式。

-- 查看几种常见模式的数量 SELECT COUNT(*) FILTER (WHERE full_info ~ '，') as with_comma, COUNT(*) FILTER (WHERE full_info ~ '\|') as with_pipe, COUNT(*) FILTER (WHERE full_info ~ '-') as with_dash, COUNT(*) FILTER (WHERE full_info ~ '，.*\d{11}') as comma_with_phone_pattern FROM users;

这个查询帮助我们判断主流的格式，为编写正则表达式提供依据。假设我们发现大部分数据是'姓名， 手机号， 地址'的格式。

4.2 第二步：编写健壮的正则表达式进行提取

我们需要一个能匹配姓名、11位手机号、以及地址部分的正则表达式。地址部分最复杂，我们可能先整体提取，再进一步拆分。

-- 第一步：提取三大块 SELECT full_info, (REGEXP_MATCHES(full_info, '^([^，\d]+)[，|]?\s*(\d{11})[，|]?\s*(.+)$'))[1] as raw_name, (REGEXP_MATCHES(full_info, '^([^，\d]+)[，|]?\s*(\d{11})[，|]?\s*(.+)$'))[2] as raw_phone, (REGEXP_MATCHES(full_info, '^([^，\d]+)[，|]?\s*(\d{11})[，|]?\s*(.+)$'))[3] as raw_address FROM users WHERE full_info ~ '^([^，\d]+)[，|]?\s*(\d{11})[，|]?\s*(.+)$';

这个正则解释：

• ^([^，\d]+)：从开头匹配，直到遇到中文逗号或数字（手机号开头），这部分是姓名。
• [，|]?\s*：可能的分隔符和空格。
• (\d{11})：匹配11位数字的手机号。
• [，|]?\s*：可能的分隔符和空格。
• (.+)$：匹配剩下的所有字符作为地址。

4.3 第三步：精细化清洗与拆分地址

姓名和手机号已经相对干净。地址部分需要进一步拆分省、市。这里我们可以利用中国行政区划的特点，结合SUBSTRING和POSITION，或者更复杂的正则。 一个相对简单（但不完全精确）的方法是查找省、市关键词的位置。

WITH extracted AS ( -- 上一步的查询作为子查询 ) SELECT raw_name as name, raw_phone as phone, raw_address, -- 提取省：假设地址以省名开头 SUBSTRING(raw_address FROM '^(.*?省|.*?自治区|.*?市)') as province, -- 提取市：在省之后的部分找市 CASE WHEN SUBSTRING(raw_address FROM '^(.*?省|.*?自治区|.*?市)') IS NOT NULL THEN SUBSTRING( SUBSTRING(raw_address FROM (LENGTH(SUBSTRING(raw_address FROM '^(.*?省|.*?自治区|.*?市)')) + 1)), FROM '^(.*?市|.*?地区|.*?州)' ) ELSE SUBSTRING(raw_address FROM '^(.*?市|.*?地区|.*?州)') END as city FROM extracted;

重要提醒：地址解析是一个极其复杂的问题，上述SQL只是一个演示性质的简化方案。在生产环境中，面对全国地址，更可靠的做法是：

1. 使用专门的地址解析服务或库（在应用层处理）。
2. 维护一个标准的省市区字典表，通过模糊匹配或分词算法来关联。
3. 在数据源头（前端或ETL环节）就要求分字段填写。

4.4 第四步：数据验证与异常处理

清洗过程中，总会有一些“奇葩”数据不符合正则模式。我们需要将它们找出来，进行人工复核或制定更复杂的规则。

-- 找出未能被初始正则匹配的数据 SELECT full_info FROM users WHERE full_info !~ '^([^，\d]+)[，|]?\s*(\d{11})[，|]?\s*(.+)$' LIMIT 100;

处理这些异常数据，是数据清洗工作量的主要部分。

5. 常见问题与排查技巧实录

在实际操作中，你会遇到各种各样奇怪的问题。下面是我总结的一些高频问题和解决方法。

问题1：为什么SUBSTRING(‘hello’， 0， 3)返回的不是'hel'？答案与排查：因为PG的字符串索引从1开始。start_position为0时，行为是未定义或返回空字符串。永远记住索引从1开始。如果你从某个编程语言（0起始索引）转过来，这是最需要适应的点。排查时，先用SELECT ‘hello’[1]测试一下，确认环境。

问题2：处理中文时，截取结果出现了乱码或半个汉字。答案与排查：

1. 确认编码：首先确认数据库、客户端、终端的编码都是UTF-8（SHOW server_encoding;）。
2. 确认函数：检查你是否错误地使用了按字节操作的函数（如substr）。99%的文本处理应该用SUBSTRING。
3. 检查源数据：源数据本身是否已经损坏或混合了其他编码。可以用hex_encode函数查看字符的十六进制表示。

问题3：SPLIT_PART返回空字符串，但我明明觉得字段存在。答案与排查：

1. 分隔符是否正确：检查分隔符是否完全匹配，包括大小写和空格。例如，SPLIT_PART(‘a,b,c’， ‘，’， 2)（中文逗号）和SPLIT_PART(‘a,b,c’， ‘，’， 2)（英文逗号）结果不同。
2. 序号是否正确：记住序号从1开始。并且，如果字符串以分隔符开头，第一部分会是空字符串。SELECT SPLIT_PART(‘，a,b’， ‘，’， 1)返回的就是空字符串。
3. 使用TRIM函数：数据中可能存在多余空格，导致匹配失败。可以尝试SPLIT_PART(TRIM(string)， delimiter， field_num)。

问题4：在WHERE子句中使用字符串函数导致查询超慢。答案与排查：

1. 检查执行计划：使用EXPLAIN (ANALYZE， BUFFERS)查看查询计划，确认是否进行了全表扫描（Seq Scan）。
2. 应用前述优化方案：
   • 能否改用LIKE前缀匹配并创建相应索引？
   • 该查询是否频繁到值得创建表达式索引？
   • 能否通过冗余字段或物化视图来预先计算好截取结果？

问题5：如何截取字符串中第N次出现某个分隔符之后的内容？这是一个经典需求，但PG没有内置函数直接支持。解决方案是结合STRING_TO_ARRAY和ARRAY_TO_STRING。

-- 例如，获取第二个‘-’之后的所有内容 WITH test AS (SELECT 'A-B-C-D-E' as str) SELECT str, ARRAY_TO_STRING((STRING_TO_ARRAY(str, '-'))[3:]， '-') as after_second_dash FROM test; -- 返回 ‘C-D-E’

解释：STRING_TO_ARRAY(str， ‘-’)将字符串转为数组{A，B，C，D，E}。[3:]是数组切片语法，取出从第3个元素到末尾的所有元素。ARRAY_TO_STRING(…， ‘-’)再将其用‘-’连接回字符串。

字符截取，这个看似简单的操作，背后是对数据编码、函数特性、性能平衡的深刻理解。从基础的SUBSTRING到复杂的正则解析，每一步的选择都影响着结果的准确性和系统的效率。我最深的体会是，在处理字符串之前，花时间了解你的数据——它的编码、它的格式规律、它的异常情况——比盲目地写SQL要重要得多。很多时候，一个精心设计的正则表达式或一个预先的数据探查，能省下后面无数个小时的调试和重跑任务的时间。希望这些从实战中总结出的经验，能让你在下次面对“PG数据库字符截取”这个任务时，更加游刃有余。