目录
1.1 正则表达式
1.1.1 什么是正则表达式？
1.1.2 PostgreSQL 正则表达式操作符
1.1.3 基本元字符和语法
1.1.4 字符类
1.2 数字匹配模式
1.2.1 基本数字匹配
1.2.2 高级数字匹配
1.3 字母匹配模式
1.3.1 基本字母匹配
1.3.2 高级字母匹配
1.4 空白字符和特殊字符匹配
1.4.1 空白字符匹配
1.4.2 特殊字符匹配和转义
1.5 PostgreSQL 正则表达式函数
1.5.1 regexp_match（） 函数 --》 提取出数据
1.5.2 regexp_matches（） 函数
1.5.3 regexp_replace（） 函数
1.5.4 regexp_substr（）函数
1.5.4 regexp_split_to_table（） 和 regexp_split_to_array（） 函数
1.1 正则表达式
1.1.1 什么是正则表达式？
概念解释：正则表达式（Regular Expression）是一种用于描述字符串模式的特殊文本格式。它使用特定的语法规则来定义搜索模式，可以用于检查字符串是否包含某种模式、提取匹配的部分或者替换匹配的文本。在 PostgreSQL 中，正则表达式提供了比传统的 LIKE 操作符更强大和灵活的模式匹配能力。
核心特性：
模式匹配：可以定义复杂的文本模式进行匹配
文本提取：从字符串中提取符合特定模式的部分
数据验证：验证输入数据是否符合预定格式
文本替换：将匹配的文本替换为指定内容
应用场景：
电子邮件地址验证
电话号码格式检查
数据清洗和标准化
日志文件分析
复杂文本搜索
1.1.2 PostgreSQL 正则表达式操作符
概念解释：
PostgreSQL 提供了四组基本的正则表达式操作符，用于执行模式匹配操作。这些操作符区分大小写和不区分大小写两种模式，以及匹配和不匹配两种情况。
操作符分类：
~：匹配正则表达式，区分大小写。~*：匹配正则表达式，不区分大小写 。!~：不匹配正则表达式，区分大小写。!~*：不匹配正则表达式，不区分大小写。
SQL示例演示：
-- 创建测试数据表 CREATE TABLE text_samples ( id SERIAL PRIMARY KEY, description TEXT ); -- 插入测试数据 INSERT INTO text_samples (description) VALUES ('Product ABC-123 is available'), ('Item XYZ-456 is out of stock'), ('Special offer for DEF-789'), ('Regular product GHI-012'), ('Discount on JKL-345 items'); -- 区分大小写匹配 SELECT description FROM text_samples WHERE description ~ 'Product'; -- 只匹配'Product' -- 不区分大小写匹配 SELECT description FROM text_samples WHERE description ~* 'product'; -- 匹配'product'、'Product'等 -- 不匹配特定模式 SELECT description FROM text_samples WHERE description !~ 'available'; -- 返回不包含'available'的行 -- 不匹配特定模式（不区分大小写） SELECT description FROM text_samples WHERE description !~* 'OFFER'; -- 返回不包含'offer'的行（忽略大小写）1.1.3 基本元字符和语法
概念解释：
元字符是正则表达式中具有特殊含义的字符，它们用于构建复杂的匹配模式。掌握这些元字符是使用正则表达式的基础。
常用元字符：
.：匹配任意单个字符（除了换行符）*：匹配前一个字符 0 次或多次+：匹配前一个字符 1 次或多次?：匹配前一个字符 0 次或 1 次^：匹配字符串的开头$：匹配字符串的结尾|：或操作，匹配多个模式之一[]：匹配方括号内包含的任意一个字符。
SQL示例演示：
-- 创建测试表 CREATE TABLE pattern_test ( text_value TEXT ); INSERT INTO pattern_test VALUES ('abc'), ('ab'), ('a'), ('ac'), ('abcd'), ('bcd'), ('123'), ('a.b'); -- . 匹配任意单个字符 SELECT text_value FROM pattern_test WHERE text_value ~ 'a.c'; -- 匹配'abc', 'a c', 'a.c'等 -- * 匹配0次或多次 SELECT text_value FROM pattern_test WHERE text_value ~ 'ab*'; -- 匹配'a', 'ab', 'abb', 'abbb'等 -- + 匹配1次或多次 SELECT text_value FROM pattern_test WHERE text_value ~ 'ab+'; -- 匹配'ab', 'abb', 'abbb'等，不匹配'a' -- ? 匹配0次或1次 SELECT text_value FROM pattern_test WHERE text_value ~ 'ab?c'; -- 匹配'ac', 'abc' -- ^ 匹配开头 SELECT text_value FROM pattern_test WHERE text_value ~ '^a'; -- 匹配以'a'开头的字符串 -- $ 匹配结尾 SELECT text_value FROM pattern_test WHERE text_value ~ 'c$'; -- 匹配以'c'结尾的字符串 -- | 或操作 SELECT text_value FROM pattern_test WHERE text_value ~ 'abc|123'; -- 匹配'abc'或'123'1.1.4 字符类
概念解释：字符类用于定义一组字符，匹配其中任意一个字符。字符类使用方括号[]表示，可以在其中列出具体的字符或使用范围表示法。
字符类语法：
[abc]：匹配 a、b 或 c 中的任意一个字符[a-z]：匹配从 a 到 z 的任意小写字母[A-Z]：匹配从 A 到 Z 的任意大写字母[0-9]：匹配从 0 到 9 的任意数字[^abc]：匹配除了 a、b、c 之外的任意字符
SQL示例演示：
-- 创建字符类测试表 CREATE TABLE char_class_test ( text_value TEXT ); INSERT INTO char_class_test VALUES ('apple'), ('banana'), ('cherry'), ('123'), ('ABC'), ('a1b2'), ('test123'), ('hello world'); -- 匹配包含元音字母的单词 SELECT text_value FROM char_class_test WHERE text_value ~ '[aeiou]'; -- 匹配包含数字的字符串 SELECT text_value FROM char_class_test WHERE text_value ~ '[0-9]'; -- 匹配以小写字母开头的字符串 SELECT text_value FROM char_class_test WHERE text_value ~ '^[a-z]'; SELECT text_value FROM char_class_test WHERE text_value ~ '^[^a-z]'; -- 匹配以数字结尾的字符串 SELECT text_value FROM char_class_test WHERE text_value ~ '[0-9]$'; -- 匹配不包含数字的字符串 SELECT text_value FROM char_class_test WHERE text_value ~ '^[^0-9]*$'; -- 匹配字母和数字组合（开头和结尾都是字符和数字，不包含空格等内容） SELECT text_value FROM char_class_test WHERE text_value ~ '^[A-Za-z0-9]+$';1.2 数字匹配模式
1.2.1 基本数字匹配
概念解释：
数字匹配是正则表达式中最常用的功能之一，用于识别和提取字符串中的数字部分。PostgreSQL 提供了多种方式来匹配不同类型的数字格式。
数字匹配模式：
[0-9]或\d：匹配单个数字[0-9]+：匹配一个或多个连续数字[0-9]*：匹配零个或多个数字[0-9]{n}：匹配恰好 n 个数字[0-9]{n,}：匹配至少 n 个数字[0-9]{n,m}：匹配 n 到 m 个数字[0-9]{,m}：匹配 n 到 m 个数字
SQL示例演示：
-- 创建数字测试表 CREATE TABLE number_test ( text_value TEXT ); INSERT INTO number_test VALUES ('123'), ('45.67'), ('0.5'), ('-123'), ('+456.78'), ('1.23e+10'), ('12.34.56'), -- 无效数字格式 ('abc123'), ('123abc'), ('$100.50'), ('Version 2.1.0'); -- 匹配包含数字的字符串 SELECT text_value FROM number_test WHERE text_value ~ '[0-9]'; -- 匹配纯数字字符串 SELECT text_value FROM number_test WHERE text_value ~ '^[0-9]+$'; -- 匹配整数（包括正负号） SELECT text_value FROM number_test WHERE text_value ~ '^[-+]?[0-9]+$'; -- 匹配小数 SELECT text_value FROM number_test WHERE text_value ~ '^[-+]?[0-9]*\.[0-9]+$'; -- 匹配所有数字格式（整数+小数） SELECT text_value FROM number_test WHERE text_value ~ '^[-+]?[0-9]*\.?[0-9]+$'; -- 匹配科学计数法 SELECT text_value FROM number_test WHERE text_value ~ '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$'; -- 匹配特定长度的数字 SELECT text_value FROM number_test WHERE text_value ~ '[0-9]{3}'; -- 匹配恰好3位数字1.2.2 高级数字匹配
概念解释：在实际应用中，数字可能以各种格式出现，如带千分位分隔符、货币符号、百分比等。高级数字匹配需要处理这些复杂的格式。
复杂数字格式：
千分位数字：
1,234,567.89货币金额：
$100.50、€75,00百分比：
15.5%分数：
1/2、3/4范围数字：
10-20、30-40%
SQL示例演示：
-- 创建复杂数字格式测试表 CREATE TABLE complex_numbers ( text_value TEXT ); INSERT INTO complex_numbers VALUES ('1,234,567.89'), ('$100.50'), ('€75,00'), ('15.5%'), ('1/2'), ('3/4'), ('10-20'), ('30-40%'), ('Price: $199.99'), ('Discount: 25% off'); -- 匹配千分位数字 SELECT text_value FROM complex_numbers WHERE text_value ~ '^[0-9]{1,3}(,[0-9]{3})*(\.[0-9]+)?$'; -- 匹配美元金额 SELECT text_value FROM complex_numbers WHERE text_value ~ '\$[0-9,]+\.?[0-9]*'; -- 匹配欧元金额（使用逗号作为小数点） SELECT text_value FROM complex_numbers WHERE text_value ~ '€[0-9.]+,[0-9]+'; -- 匹配百分比 SELECT text_value FROM complex_numbers WHERE text_value ~ '[0-9]+\.?[0-9]*%'; -- 匹配分数 SELECT text_value FROM complex_numbers WHERE text_value ~ '[0-9]+/[0-9]+'; -- 匹配数字范围 SELECT text_value FROM complex_numbers WHERE text_value ~ '[0-9]+-[0-9]+'; -- 提取纯数字部分（去除符号和分隔符） SELECT text_value, regexp_replace(text_value, '[^0-9.]', '', 'g') as numbers_only FROM complex_numbers WHERE text_value ~ '[0-9]';1.3 字母匹配模式
1.3.1 基本字母匹配
概念解释：字母匹配用于识别和操作文本中的字母字符。根据需求，可以匹配特定大小写的字母、特定范围的字母或者所有字母字符。
字母匹配模式：
[a-z]：匹配任意小写字母[A-Z]：匹配任意大写字母[A-Za-z]：匹配任意字母（不区分大小写）[A-Za-z]+：匹配一个或多个字母^[A-Za-z]+$：匹配纯字母字符串\b[A-Za-z]+\b：匹配完整的单词
SQL示例演示：
-- 创建字母测试表 CREATE TABLE letter_test ( text_value TEXT ); INSERT INTO letter_test VALUES ('Hello'), ('WORLD'), ('test123'), ('123test'), ('hello world'), ('café'), (' naïve'), ('user_name'), ('first-name'), ('Email@Address'); -- 匹配包含字母的字符串 SELECT text_value FROM letter_test WHERE text_value ~ '[A-Za-z]'; -- 匹配纯大写字母字符串 SELECT text_value FROM letter_test WHERE text_value ~ '^[A-Z]+$'; -- 匹配纯小写字母字符串 SELECT text_value FROM letter_test WHERE text_value ~ '^[a-z]+$'; -- 匹配纯字母字符串（大小写混合） SELECT text_value FROM letter_test WHERE text_value ~ '^[A-Za-z]+$'; -- 匹配以字母开头的字符串 SELECT text_value FROM letter_test WHERE text_value ~ '^[A-Za-z]'; -- 匹配以字母结尾的字符串 SELECT text_value FROM letter_test WHERE text_value ~ '[A-Za-z]$'; -- 匹配完整的英文单词 SELECT text_value FROM letter_test WHERE text_value ~ '\b[A-Za-z]+\b'; -- 匹配特定长度的单词 SELECT text_value FROM letter_test WHERE text_value ~ '\b[A-Za-z]{4,6}\b'; -- 匹配4-6个字母的单词1.3.2 高级字母匹配
概念解释：在实际文本处理中，字母匹配可能需要考虑更复杂的情况，如带重音符号的字母、特定语言的字符、字母数字组合等。
高级字母匹配场景：
国际化字符：重音字母、变音符号
字母数字标识符：用户名、产品代码
特定格式的单词：驼峰命名、蛇形命名
单词边界和位置匹配
SQL示例演示：
-- 创建高级字母测试表 CREATE TABLE advanced_letters ( text_value TEXT ); INSERT INTO advanced_letters VALUES ('café'), ('naïve'), (' façade'), ('Müller'), ('userName'), ('user_name'), ('UserID123'), ('XMLParser'), ('json_format'), ('MySQL_Database'), ('PostgreSQL'), ('C#_Program'); -- 匹配包含重音字母的单词 SELECT text_value FROM advanced_letters WHERE text_value ~ '[àáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ]'; -- 匹配驼峰命名（以大写字母开头的单词） SELECT text_value FROM advanced_letters WHERE text_value ~ '[A-Z][a-z]*[A-Z][a-z]*'; -- 匹配蛇形命名（小写字母加下划线） SELECT text_value FROM advanced_letters WHERE text_value ~ '^[a-z]+(_[a-z]+)*$'; -- 匹配字母数字组合（常见标识符格式） SELECT text_value FROM advanced_letters WHERE text_value ~ '^[A-Za-z_][A-Za-z0-9_]*$'; -- 匹配包含大写字母的单词 SELECT text_value FROM advanced_letters WHERE text_value ~ '[A-Z]'; -- 匹配特定前缀的单词 SELECT text_value FROM advanced_letters WHERE text_value ~ '\b(user|mysql|postgres)\w*\b'; -- 提取所有单词（包括带特殊字符的） SELECT text_value, regexp_matches(text_value, '[A-Za-zÀ-ÿ]+', 'g') as words FROM advanced_letters;1.4 空白字符和特殊字符匹配
1.4.1 空白字符匹配
概念解释：
空白字符匹配用于处理文本中的空格、制表符、换行符等不可见字符。这在数据清洗和文本格式化中非常有用。
空白字符元字符：
\s：匹配任何空白字符（空格、制表符、换行符等）\S：匹配任何非空白字符\n：匹配换行符\t：匹配制表符\r：匹配回车符
SQL示例演示：
-- 创建空白字符测试表 CREATE TABLE whitespace_test ( text_value TEXT ); INSERT INTO whitespace_test VALUES ('Hello World'), ('Hello World'), -- 多个空格 ('Hello' || chr(9) || 'World'), -- 制表符 ('Hello' || chr(10) || 'World'), -- 换行符 (' Leading spaces'), ('Trailing spaces '), ('NoSpacesHere'), ('Mixed ' || chr(9) || ' whitespace'); -- 匹配包含空白字符的字符串 SELECT text_value FROM whitespace_test WHERE text_value ~ '\s'; -- 匹配以空白字符开头的字符串 SELECT text_value FROM whitespace_test WHERE text_value ~ '^\s'; -- 匹配以空白字符结尾的字符串 SELECT text_value FROM whitespace_test WHERE text_value ~ '\s$'; -- 匹配多个连续空白字符 SELECT text_value FROM whitespace_test WHERE text_value ~ '\s{2,}'; -- 匹配不包含空白字符的字符串 SELECT text_value FROM whitespace_test WHERE text_value ~ '^\S*$'; -- 标准化空白字符（将多个空白字符替换为单个空格） SELECT text_value, regexp_replace(text_value, '\s+', ' ', 'g') as normalized FROM whitespace_test; -- 去除首尾空白字符 SELECT text_value, regexp_replace(regexp_replace(text_value, '^\s+', ''), '\s+$', '') as trimmed FROM whitespace_test;1.4.2 特殊字符匹配和转义
概念解释：特殊字符在正则表达式中具有特殊含义，如果要匹配这些字符的字面值，需要进行转义。转义使用反斜杠\字符。
需要转义的特殊字符：
. * + ? ^ $ { } [ ] ( ) | \这些字符在正则表达式中都有特殊含义，要匹配它们本身需要在前面加
\
SQL示例演示：
-- 创建特殊字符测试表 CREATE TABLE special_chars_test ( text_value TEXT ); INSERT INTO special_chars_test VALUES ('File: document.txt'), ('Price: $100.00'), ('Email: user@example.com'), ('Path: C:\Windows\System32'), ('Regex: a*b+c?d'), ('Special: .*+?^${}[]()|\'), ('Normal text'), ('10 * 20 = 200'), ('50% discount'); -- 匹配点号（字面意义） SELECT text_value FROM special_chars_test WHERE text_value ~ '\.txt'; -- 匹配美元符号 SELECT text_value FROM special_chars_test WHERE text_value ~ '\$'; -- 匹配at符号 SELECT text_value FROM special_chars_test WHERE text_value ~ '@'; -- 匹配反斜杠 SELECT text_value FROM special_chars_test WHERE text_value ~ '\\\\'; -- 匹配星号（字面意义） SELECT text_value FROM special_chars_test WHERE text_value ~ '\*'; -- 匹配加号（字面意义） SELECT text_value FROM special_chars_test WHERE text_value ~ '\+'; -- 匹配问号（字面意义） SELECT text_value FROM special_chars_test WHERE text_value ~ '\?'; -- 创建转义函数 CREATE OR REPLACE FUNCTION regexp_escape(text) RETURNS TEXT AS $$ SELECT regexp_replace($1, '([.*+?^${}()|\[\]\\])', '\\\1', 'g'); $$ LANGUAGE sql IMMUTABLE; -- 使用转义函数进行安全匹配 SELECT text_value, regexp_escape(text_value) as escaped_text FROM special_chars_test WHERE text_value ~ regexp_escape('a*b+c?d');1.5 PostgreSQL 正则表达式函数
1.5.1 regexp_match（） 函数 --》 提取出数据
概念解释：
regexp_match()函数用于从字符串中提取匹配正则表达式的第一个子字符串。它返回一个文本数组，包含所有捕获组匹配的内容。如果没有找到匹配，则返回 NULL。
函数语法：
regexp_match(string, pattern [, flags ])参数说明：
string：要搜索的输入字符串pattern：正则表达式模式flags：可选参数，控制匹配行为（如 'i' 表示不区分大小写）
SQL示例演示：
-- 创建测试数据 CREATE TABLE match_test ( text_value TEXT ); INSERT INTO match_test VALUES ('Product: ABC-123, Price: $100'), ('Item: XYZ-456, Cost: €75.50'), ('Code: DEF-789, Amount: ¥5000'), ('No product code here'), ('Multiple: GHI-012 and JKL-345'); -- 基本匹配：提取产品代码 SELECT text_value, regexp_match(text_value, '[A-Z]{3}-[0-9]{3}') as product_code FROM match_test; -- 使用捕获组分别提取字母和数字部分 SELECT text_value, regexp_match(text_value, '([A-Z]{3})-([0-9]{3})') as code_parts, (regexp_match(text_value, '([A-Z]{3})-([0-9]{3})'))[1] as letters, (regexp_match(text_value, '([A-Z]{3})-([0-9]{3})'))[2] as numbers FROM match_test WHERE text_value ~ '[A-Z]{3}-[0-9]{3}'; -- 提取金额（带货币符号） SELECT text_value, regexp_match(text_value, '[$€¥][0-9]+\.?[0-9]*') as amount FROM match_test WHERE text_value ~ '[$€¥][0-9]'; -- 使用标志进行不区分大小写匹配 SELECT text_value, regexp_match(text_value, 'product', 'i') as matched_product FROM match_test WHERE text_value ~* 'product'; -- 处理没有匹配的情况 SELECT text_value, COALESCE( (regexp_match(text_value, '[A-Z]{3}-[0-9]{3}'))[1], 'No code' ) as product_code FROM match_test;1.5.2 regexp_matches（） 函数
概念解释：regexp_matches()函数返回字符串中所有匹配正则表达式的子字符串。与regexp_match()不同，它返回所有匹配的结果，而不仅仅是第一个匹配。
函数语法：
regexp_matches(string, pattern [, flags ])重要特性：
返回所有匹配，而不仅仅是第一个
必须使用 'g' 标志来获取所有匹配
返回结果是一个集合，每行一个匹配
SQL示例演示：
-- 创建包含多个匹配的测试数据 CREATE TABLE multiple_matches ( text_value TEXT ); INSERT INTO multiple_matches VALUES ('Colors: red, blue, green, yellow'), ('Numbers: 123, 456, 789'), ('Products: ABC-123, DEF-456, GHI-789'), ('Mixed: item1, ITEM2, Item3'), ('No matches here'), ('Email: john@test.com, jane@sample.org, bob@example.net'); -- 提取所有颜色单词 SELECT text_value, regexp_matches(text_value, '\b[a-z]+\b', 'g') as colors FROM multiple_matches WHERE text_value ~ 'Colors'; -- 提取所有数字 SELECT text_value, regexp_matches(text_value, '[0-9]+', 'g') as numbers FROM multiple_matches WHERE text_value ~ '[0-9]'; -- 提取所有产品代码 SELECT text_value, regexp_matches(text_value, '[A-Z]{3}-[0-9]{3}', 'g') as product_codes FROM multiple_matches WHERE text_value ~ '[A-Z]{3}-[0-9]{3}'; -- 提取所有电子邮件地址 SELECT text_value, regexp_matches(text_value, '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}', 'g') as emails FROM multiple_matches WHERE text_value ~ '@'; -- 统计匹配数量 WITH matches AS ( SELECT text_value, regexp_matches(text_value, '[A-Za-z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}', 'g') as email_match FROM multiple_matches ) SELECT text_value, COUNT(*) as email_count FROM matches GROUP BY text_value ORDER BY email_count DESC;1.5.3 regexp_replace（） 函数
概念解释：regexp_replace()函数用于替换字符串中匹配正则表达式的部分。它可以用于数据清洗、格式化和文本转换。
函数语法：
regexp_replace(string, pattern, replacement [, flags ])替换功能：
简单的文本替换
使用捕获组进行智能替换
全局替换或只替换第一个匹配
复杂的文本转换
SQL示例演示：
-- 创建需要清洗的数据表 CREATE TABLE replace_test ( text_value TEXT ); INSERT INTO replace_test VALUES ('Phone: 555-123-4567'), ('Date: 2024-01-15'), ('Price: $100.50'), ('Email: USER@EXAMPLE.COM'), ('Text with multiple spaces'), ('MixedCaseText'), ('123-45-6789'), -- SSN格式 ('C:\Users\John\Documents'); -- 基本替换：格式化电话号码 SELECT text_value, regexp_replace(text_value, '(\d{3})-(\d{3})-(\d{4})', '(\1) \2-\3') as formatted_phone FROM replace_test WHERE text_value ~ '\d{3}-\d{3}-\d{4}'; -- 日期格式转换 SELECT text_value, regexp_replace(text_value, '(\d{4})-(\d{2})-(\d{2})', '\3/\2/\1') as european_date FROM replace_test WHERE text_value ~ '\d{4}-\d{2}-\d{2}'; -- 标准化电子邮件（转为小写） SELECT text_value, regexp_replace(text_value, '([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})', LOWER('\1')) as normalized_email FROM replace_test WHERE text_value ~ '@'; -- 清理多余空白字符 SELECT text_value, regexp_replace(text_value, '\s+', ' ') as cleaned_text FROM replace_test; -- 掩码敏感信息 SELECT text_value, regexp_replace(text_value, '(\d{3})-(\d{2})-(\d{4})', 'XXX-XX-\3') as masked_ssn FROM replace_test WHERE text_value ~ '\d{3}-\d{2}-\d{4}'; -- 路径格式转换（Unix风格） SELECT text_value, regexp_replace(text_value, '\\\\', '/', 'g') as unix_path FROM replace_test WHERE text_value ~ '\\\\'; -- 驼峰命名转蛇形命名 SELECT text_value, LOWER(regexp_replace(text_value, '([a-z])([A-Z])', '\1_\2', 'g')) as snake_case FROM replace_test WHERE text_value ~ '[a-z][A-Z]';1.5.4regexp_substr（）函数
regexp_substr是PostgreSQL14引入的 正则提取函数，用来从字符串中返回第一个匹配正则模式的子串。
语法、参数、返回值、可选标志、异常、性能、版本差异、与Oracle的兼容度、典型场景一次讲透。
（1）函数原型
regexp_substr ( source_text text, -- 被搜索的字符串 pattern text, -- POSIX 正则表达式 start_pos int DEFAULT 1,-- 从第几个字符开始搜（1-based） occurrence int DEFAULT 1,-- 返回第几次匹配 flags text DEFAULT ''-- 零个或多个单字母标志 ) RETURNS text匹配失败返回NULL。
（2）参数详解
参数 | 类型 | 说明 |
|---|---|---|
source_text | text | 任意长度字符串，NULL 输入直接返回 NULL。 |
pattern | text | POSIX正则，支持捕获组、前后瞻、字符类等；为空串抛 |
start_pos | int | 1-based; ≤0 抛 |
occurrence | int | 1 表示第一次匹配；≤0 抛 |
flags | text | 大小写敏感、换行模式等，后文单列。 |
（3）支持的正则标志（flags）
可组合使用，顺序无关。
字母 | 含义 | 等价内联 |
| 大小写不敏感 |
|
| 大小写敏感（默认） |
|
| 让 |
|
| 多行模式， |
|
| 同 |
|
例：
regexp_substr('A\nb', '(?m)a', 1, 1, 'i') → 'A'（4）返回值与捕获组
不带捕获组：返回**整个匹配文本**。
带捕获组：返回**第一个捕获组**的内容（Oracle 兼容行为）。若想返回整个匹配，请把正则写成非捕获形式或使用括号包裹整段。
SELECT regexp_substr('id=123;name=Tom;', 'name=([^;]+)'); -- 返回 Tom1.5.4 regexp_split_to_table（） 和 regexp_split_to_array（） 函数
概念解释：这两个函数用于根据正则表达式模式拆分字符串：
regexp_split_to_table()：将字符串拆分为多行记录regexp_split_to_array()：将字符串拆分为数组
函数语法：
regexp_split_to_table(string, pattern [, flags ]) regexp_split_to_array(string, pattern [, flags ])应用场景：
解析 CSV 数据
分词处理
日志文件分析
数据规范化
SQL示例演示：
-- 创建需要拆分的测试数据 CREATE TABLE split_test ( text_value TEXT ); INSERT INTO split_test VALUES ('apple,banana,cherry,date'), ('red|green|blue|yellow'), ('John Doe;Jane Smith;Bob Johnson'), ('word1 word2 word3 word4'), -- 不规则空格 ('1.2.3.4.5'), ('key=value;name=test;type=string'), ('Mixed,separators;and|different formats'); -- 使用 regexp_split_to_table 拆分CSV SELECT text_value, regexp_split_to_table(text_value, ',') as item FROM split_test WHERE text_value ~ ','; -- 使用 regexp_split_to_array 拆分为数组 SELECT text_value, regexp_split_to_array(text_value, ',') as items_array FROM split_test WHERE text_value ~ ','; -- 拆分不规则分隔的单词 SELECT text_value, regexp_split_to_table(text_value, '\s+') as word FROM split_test WHERE text_value ~ '[a-zA-Z]+\s+[a-zA-Z]+'; -- 解析键值对 SELECT text_value, regexp_split_to_table(text_value, '[;]') as key_value_pair FROM split_test WHERE text_value ~ '='; -- 进一步拆分键值对 WITH pairs AS ( SELECT text_value, regexp_split_to_table(text_value, '[;]') as pair FROM split_test WHERE text_value ~ '=' ) SELECT text_value, pair, split_part(pair, '=', 1) as key, split_part(pair, '=', 2) as value FROM pairs; -- 处理混合分隔符 SELECT text_value, regexp_split_to_table(text_value, '[,;|]') as element FROM split_test WHERE text_value ~ '[,;|]'; -- 统计拆分后的元素数量 SELECT text_value, array_length(regexp_split_to_array(text_value, '[,;|\s]+'), 1) as element_count FROM split_test;
