目录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或1231.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.23e10), (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), (EmailAddress); -- 匹配包含字母的字符串 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: userexample.com), (Path: C:\Windows\System32), (Regex: a*bc?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*bc?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: johntest.com, janesample.org, bobexample.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: USEREXAMPLE.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_texttext任意长度字符串NULL 输入直接返回 NULL。patterntextPOSIX正则支持捕获组、前后瞻、字符类等为空串抛ERROR。start_posint1-based; ≤0 抛ERROR大于源串长度返回 NULL。occurrenceint1 表示第一次匹配≤0 抛ERROR大于实际匹配数返回 NULL。flagstext大小写敏感、换行模式等后文单列。3支持的正则标志flags可组合使用顺序无关。字母含义等价内联i大小写不敏感(?i)c大小写敏感默认(?c)n让.匹配换行符(?n)m多行模式^/$匹配行首行尾(?m)s同n兼容 Oracle(?s)例regexp_substr(A\nb, (?m)a, 1, 1, i) → A4返回值与捕获组不带捕获组返回**整个匹配文本**。带捕获组返回**第一个捕获组**的内容Oracle 兼容行为。若想返回整个匹配请把正则写成非捕获形式或使用括号包裹整段。SELECT regexp_substr(id123;nameTom;, 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), (keyvalue;nametest;typestring), (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;
【AI大数据工程师特训笔记】第11讲：正则表达式与正则函数
发布时间：2026/6/11 1:13:04
目录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或1231.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.23e10), (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), (EmailAddress); -- 匹配包含字母的字符串 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: userexample.com), (Path: C:\Windows\System32), (Regex: a*bc?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*bc?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: johntest.com, janesample.org, bobexample.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: USEREXAMPLE.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_texttext任意长度字符串NULL 输入直接返回 NULL。patterntextPOSIX正则支持捕获组、前后瞻、字符类等为空串抛ERROR。start_posint1-based; ≤0 抛ERROR大于源串长度返回 NULL。occurrenceint1 表示第一次匹配≤0 抛ERROR大于实际匹配数返回 NULL。flagstext大小写敏感、换行模式等后文单列。3支持的正则标志flags可组合使用顺序无关。字母含义等价内联i大小写不敏感(?i)c大小写敏感默认(?c)n让.匹配换行符(?n)m多行模式^/$匹配行首行尾(?m)s同n兼容 Oracle(?s)例regexp_substr(A\nb, (?m)a, 1, 1, i) → A4返回值与捕获组不带捕获组返回**整个匹配文本**。带捕获组返回**第一个捕获组**的内容Oracle 兼容行为。若想返回整个匹配请把正则写成非捕获形式或使用括号包裹整段。SELECT regexp_substr(id123;nameTom;, 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), (keyvalue;nametest;typestring), (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;
