# 截取字符串 在数据仓库多维分析场景中，**字符串截取**是支撑业务分析的关键技术能力。 典型场景包括： - **提取核心信息**：对复合订单号进行智能分割，例如将"EC2023-BJ-0129A"拆解出核心序列号"0129A"，在18位身份证编码中使用substring()函数同时捕获行政区划代码、出生年月信息等。 - **脱敏数据**：用户隐私数据处理，例如通过right()函数精准提取手机号末4位实现数据脱敏。 - **精准提取信息** ：支持**按正则表达式**进行匹配提取，从而精准提取有效信息，例如通过regexp_matches函数匹配出多个以b字母开头的字符串。 - **清除冗余数据**：例如清除某个订单号信息前后的多余空格或空字符。 DWS提供多种截取字符串的函数供用户选择。本章节中函数所使用的示例大部分以英文字符串示例为主，如果示例中存在中文字符串，则一般指UTF-8字符编码下的场景示例，字符编码在创建数据库时指定，参见[CREATE DATABASE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0156.html)。 表1截取字符串常用函数 | 分类 | 功能 | 对应函数 | 简单示例 | 使用区别 | |:---|:---|:---|:---|:---| | 去除两端字符 | 去除字符串两端指定的字符或空格。 | [btrim(string text \[, characters text\])] | ``` SELECT btrim(' sting '); btrim ------- sting (1 row) ``` | - | | 提取子串，单位字节 | 提取子串，从第几个字节开始提取，一共提取几个字节。 | [substrb(string,from,count)] | ``` SELECT substrb('ABCDEF',2,2); substrb --------- BC (1 row) ``` | 四个函数的功能很相似，区别是提取剩余完整部分，还是只提取中间几个字节，入参是text类型，还是所有类字符串。 | | 提取子串，单位字节 | 提取子串，从第几个字节开始提取，一共提取几个字节。功能同上，区别是强调入参类型是text。 | [substrb(text,int,int)] | - | 四个函数的功能很相似，区别是提取剩余完整部分，还是只提取中间几个字节，入参是text类型，还是所有类字符串。 | | 提取子串，单位字节 | 提取子串，从第几个字节开始提取，提取剩余完整部分，当from是负数时，可以表示截取后边n个字节。 | [substrb(string,from)] | ``` SELECT substrb('ABCDEF',-2); substrb --------- EF (1 row) ``` | 四个函数的功能很相似，区别是提取剩余完整部分，还是只提取中间几个字节，入参是text类型，还是所有类字符串。 | | 提取子串，单位字节 | 提取子串，从第几个字节开始提取，提取剩余完整部分。功能同上，区别是强调入参类型是text。 | [substrb(text,int)] | ``` SELECT substrb('string',2); substrb --------- tring (1 row) ``` | 四个函数的功能很相似，区别是提取剩余完整部分，还是只提取中间几个字节，入参是text类型，还是所有类字符串。 | | 提取子串，单位字符 | 提取子串，从第几个字符开始提取，一共提取几个字符。 | [substr(string,from,count)] | ``` SELECT substr('database',0,4); substr -------- data (1 row) ``` | 是提取剩余完整部分，还是只提取中间几个字符，[substr(string,from,count)]与[substring(string \[from int\] \[for int\])]高度相似，后者是标准SQL，前者更简洁。 | | 提取子串，单位字符 | 提取子串，从第几个字符开始提取，提取剩余完整部分。 | [substr(string,from)] | ``` SELECT substr('database',5); substr -------- base (1 row) ``` | 是提取剩余完整部分，还是只提取中间几个字符，[substr(string,from,count)]与[substring(string \[from int\] \[for int\])]高度相似，后者是标准SQL，前者更简洁。 | | 提取子串，单位字符 | 提取子串，从第几个字符开始提取，一共提取几个字符。与[substr(string,from,count)]功能一致。 | [substring(string \[from int\] \[for int\])] | - | 是提取剩余完整部分，还是只提取中间几个字符，[substr(string,from,count)]与[substring(string \[from int\] \[for int\])]高度相似，后者是标准SQL，前者更简洁。 | | 提取子串，单位字符 | 提取前几位字符。 | [left(str text, n int)] | - | - | | 提取子串，单位字符 | 提取后几位字符。 | [right(str text, n int)] | - | - | | 按正则表达式提取子串 | 提取子串，匹配某种表达式，符合了才截取成功。 | [substring(string from pattern)] | 单击左侧链接跳转查看。 | regexp_substr函数与substring函数类似，substring函数是SQL标准，regexp_substr属于扩展函数，正则能力更强。 regexp_matches返回类型为文本数组，适合提取分组信息的场景。 | | 按正则表达式提取子串 | 提取子串，匹配某种表达式，符合了才截取成功，支持自定义转义符。 | [substring(string from pattern for escape)] | 单击左侧链接跳转查看。 | regexp_substr函数与substring函数类似，substring函数是SQL标准，regexp_substr属于扩展函数，正则能力更强。 regexp_matches返回类型为文本数组，适合提取分组信息的场景。 | | 按正则表达式提取子串 | 功能同上类似，但是正则能力强。 | [regexp_substr(text,text)] | 单击左侧链接跳转查看。 | regexp_substr函数与substring函数类似，substring函数是SQL标准，regexp_substr属于扩展函数，正则能力更强。 regexp_matches返回类型为文本数组，适合提取分组信息的场景。 | | 按正则表达式提取子串 | 功能同上类似，但是正则能力强。 | [regexp_substr(source_char, pattern)] | 单击左侧链接跳转查看。 | regexp_substr函数与substring函数类似，substring函数是SQL标准，regexp_substr属于扩展函数，正则能力更强。 regexp_matches返回类型为文本数组，适合提取分组信息的场景。 | | 按正则表达式提取子串 | 提取子串，但是返回类型是文本数组，尤其适合需要多条件匹配或提取分组信息的场景。 | [regexp_matches(string text, pattern text \[, flags text\])] | 单击左侧链接跳转查看。 | regexp_substr函数与substring函数类似，substring函数是SQL标准，regexp_substr属于扩展函数，正则能力更强。 regexp_matches返回类型为文本数组，适合提取分组信息的场景。 | #### btrim(string text \[, characters text\]) 描述：去除字符串（string）两端指定的字符。仅处理两端字符，中间字符即使匹配也不会被移除。 返回值类型：text 参数说明： - string，必选，目标字符串。 - characters，可选，要在目标字符串两端移除的字符。characters参数缺省时，默认去除所有的空白字符。 示例： ``` SELECT btrim('string' , 'ing'); btrim ------- str (1 row) SELECT btrim(' string '); --不指定characters，默认去除多余空格 btrim ------- string (1 row) SELECT btrim('xxdataxxbasexx','x'); --注意只去除两端的x，中间有x也不会去除。 btrim ------- dataxxbase (1 row) ``` #### substrb(string,from,count) 描述：从字符串中截取指定长度的子字符串。from表示截取的起始位置，count表示截取的子字符串长度。 该函数和[substr(string,from,count)]函数功能一致，但是计算单位为字节。 - from为0时，按1处理。 - from为正数时，截取从from开始的count个字节。 - from为负数时，截取从倒数第n个开始的count个字节，n为from的绝对值。 - count小于1时，返回null。 返回值类型：bytea 示例： ``` SELECT substrb('ABCDEF',2,2); substrb --------- BC (1 row) ``` #### substrb(text,int,int) 描述：从字符串中提取子字符串，第一个int表示从第几个字节位置开始提取，第二个int表示提取几个字节。 返回值类型：text 示例： ``` SELECT substrb('string',2,3); ---从第2个字节“t”开始，提取3个字节。 substrb --------- tri (1 row) SELECT substrb('学习数据库知识',7,9); --UTF8编码下，一个中文占3个字节，从第7个字节位置开始，即跳过2个中文字，从“数”开始，截取9个字节（3个中文） substrb --------- 数据库 (1 row) SELECT substrb('学习数据库知识',7,9); --GBK编码下，一个中文占2个字节，从第7个字节位置开始，即跳过3个中文字，从“据”开始，截取9个字节（4个中文） substrb --------- 据库知识 (1 row) ``` #### substrb(string,from) 描述：从字符串中截取指定的子字符串，from表示截取的起始位置。该函数和[substr(string,from)]函数功能一致，但是计算单位为字节。 - from为0时，按1处理。 - from为正数时，截取从from到末尾的所有字节。 - from为负数时，截取字符串的后n个字节，n为from的绝对值。 返回值类型：bytea 示例： ``` SELECT substrb('ABCDEF',-2); substrb --------- EF (1 row) ``` #### substrb(text,int) 描述：从字符串中提取子字符串，int表示从第几个字节开始提取，提取后面剩余的完整部分。 返回值类型：text 示例： ``` SELECT substrb('string',2); substrb --------- tring (1 row) SELECT substrb('学习数据库知识',7); --UTF8编码下，一个中文占3个字节，从第7个字节位置开始，即跳过2个中文字，从“数”开始截取后面完整部分 substrb --------- 数据库知识 (1 row) ``` #### substr(string,from,count) 描述：从字符串中截取指定长度的子字符串。 from表示截取的起始位置，count表示截取的子字符串长度。 - ORA和TD兼容模式下，from为0时，按1处理；MySQL兼容模式下，from为0时，返回空串。 - from为正数时，截取从from开始的count个字符。 - from为负数时，截取从倒数第n个开始的count个字符，n为from的绝对值。 - count小于1时，返回null。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) from为0或负数时，MySQL兼容模式下该函数的返回值与数据库GUC参数behavior_compat_options的配置项disable_compat_substr_mysql也相关，具体可参考[behavior_compat_options](https://support.huaweicloud.com/devg-dws/dws_04_0931.html#section2)参数兼容性配置项disable_compat_substr_mysql说明。 返回值类型：varchar 示例： ORA和TD兼容模式下，from为0时，截取"database"从第1个字符开始的4个字符。 ``` ora_compatibility_db=# SELECT substr('database',0,4); substr -------- data (1 row) td_compatibility_db=# SELECT substr('database',0,4); substr -------- data (1 row) ``` MySQL兼容下，from为0时，返回空串。 ``` mysql_compatibility_db=# SELECT substr('database',0,4); substr -------- (1 row) ``` from为正数时，截取"database"从第5个字符开始的4个字符。 ``` SELECT substr('database',5,4); substr -------- base (1 row) ``` from为负数时，截取"database"从倒数第4个字符开始的3个字符。 ``` SELECT substr('database',-4,3); substr -------- bas (1 row) ``` #### substr(string,from) 描述：从字符串中截取指定的子字符串。 from表示截取的起始位置。 - ORA和TD兼容模式下，from为0时，按1处理；MySQL兼容模式下，from为0时，返回空串。 - from为正数时，截取从from到末尾的所有字符。 - from为负数时，截取字符串的后n个字符，n为from的绝对值。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) from为0或负数时，MySQL兼容模式下该函数的返回值与数据库GUC参数behavior_compat_options的配置项disable_compat_substr_mysql也相关，具体可参考[behavior_compat_options](https://support.huaweicloud.com/devg-dws/dws_04_0931.html#section2)参数兼容性配置项disable_compat_substr_mysql说明。 返回值类型：varchar 示例： - ORA和TD兼容模式下，from为0时，截取"database"从第1个字符至末尾的所有字符。 ``` ora_compatibility_db=# SELECT substr('database',0); substr ---------- database (1 row) td_compatibility_db=# SELECT substr('database',0); substr ---------- database (1 row) ``` - MySQL兼容下，from为0时，返回空串。 ``` mysql_compatibility_db=# SELECT substr('database',0); substr -------- (1 row) ``` - from为正数时，截取"database"从第5个字符至末尾的所有字符。 ``` SELECT substr('database',5); substr -------- base (1 row) ``` - from为负数时，截取"database"从倒数第4个字符开始至末尾的所有字符。 ``` SELECT substr('database',-4); substr -------- base (1 row) ``` #### substring(string \[from int\] \[for int\]) 描述：截取子字符串，from int表示从第几个字符开始截取，for int表示截取几个字符。 返回值类型：text 示例： 从字符串"Thomas"的第2个字符"h"开始连续截取3个字符，即返回"hom"。 ``` SELECT substring('Thomas' from 2 for 3); substring ----------- hom (1 row) ``` #### left(str text, n int) 描述：返回字符串的前n个字符。 - ORA和TD兼容模式下，当n是负数时，返回除最后\|n\|个字符以外的所有字符。 - MySQL兼容模式下，当n是负数时，返回空串。 返回值类型：text 示例： ``` SELECT left('database', 4); left ------ data (1 row) ``` #### right(str text, n int) 描述：返回字符串中的后n个字符。 - ORA和TD兼容模式下，当n是负数时，返回除前\|n\|个字符以外的所有字符。 - MySQL兼容模式下，当n是负数时，返回空串。 返回值类型：text 示例： ``` SELECT right('abcde', 2); right ------- de (1 row) SELECT right('abcde', -2); right ------- cde (1 row) ``` #### substring(string from *pattern*) 描述：截取匹配POSIX正则表达式（也称为模式）的子字符串，其中string表示原始字符串，pattern表示待匹配的模式。如果该字符串没有匹配上模式则返回空值，否则返回文本中匹配到模式的那一部分。 返回值类型：text 示例： ``` SELECT substring('Thomas' from '...$'); ---匹配末尾3个连续字符 substring ----------- mas (1 row) SELECT substring('Thomas' from '^...'); ---匹配开头3个连续字符 substring ----------- Tho (1 row) SELECT substring('Tom' from '....$'); ---匹配末尾4个连续字符，Tom仅3个字符，无法匹配条件，结果为空 substring ----------- (1 row) SELECT substring('order_20251120_12345' from '_([0-9]+)_'); ---获取两个“_”之间的订单号 substring ----------- 20251120 (1 row) SELECT substring('tel:13812345678,status:valid' from 'tel:([0-9]{11})'); ---提取手机号信息 substring ----------- 13812345678 (1 row) SELECT substring('foobar' from 'o(.)b'); --匹配o(.)b规则， o表示匹配第一个字母o， (.)表示匹配任意一个字符，并用括号捕获它， b表示匹配到字母b。 'foobar' 中，'oob' 是匹配的子串，括号位置取的是子串的中间字符，所以结果是第二个o。 substring -------- o (1 row) SELECT substring('foobar' from '(o(.)b)'); --嵌套捕获规则，外层第一个捕获组，匹配到oob，内层第二个捕获组，匹配到第3个o，函数结果仅返回第一个捕获组，因此结果为oob substring -------- oob (1 row) ``` ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) 如果POSIX正则表达式模式包含任何圆括号，那么将返回匹配第一对子表达式（对应第一个左圆括号的） 的文本。如果想在表达式里使用圆括号而又不想导致这个例外，那么可以在整个表达式外边加上一对圆括号。 #### substring(string from *pattern* for *escape*) 描述：截取匹配SQL正则表达式的子字符串，其中string表示原始字符串，pattern表示匹配的模式，escape表示转义符，通过for进行指定。如果没有匹配到模式则返回空值。 为了标识在成功的时候应该返回的模式部分，模式必须包含转义字符的两次出现，并且每个转义字符后面要跟上双引号（"）。如果匹配这两个标记之间的模式的文本将被返回。 返回值类型：text 示例： ``` SELECT substring('Thomas' from '%#"o_a#"_' for '#'); substring ----------- oma (1 row) ``` **示例解读**： 1. **%#"o_a#"_** 为匹配的模式，**#**为转义符。 2. **%** 表示匹配任意数量的任意前缀的通配符，该例子匹配到前缀**Tho**。 3. #"为一个标记，该语法的目的是要匹配两个#"之间包含的文本，即**o_a**。 4. 最后一个_表示匹配到后缀字符**s**。 综上，整个匹配模式体**%#"o_a#"_**最后展开来是 % o_a _，含义变为匹配「任意前缀」+ o + 「单个任意字符」 + a + 「单个任意字符」。 字符串Thomas如果需要匹配上这个模式的话，即只截取两个#'之间的文本，即o_a，其中_可以表示通配任何字符，所以结果是**oma**。 **拓展示例**： 已知快递单号FX**5892**20251124，结构为FX + 4位单号 + 8位时间戳，实际系统只需要捕捉FX后面的4位，则可以通过以下写法获取。 ``` SELECT substring('FX589220251124' from 'FX#"____#"%' for '#'); substring ----------- 5892 (1 row) SELECT substring('FX564320241024' from 'FX#"____#"%' for '#'); substring ----------- 5643 (1 row) ``` #### regexp_substr(text,text) 描述：正则表达式的抽取子串函数。第一个text表示待处理的原始字符串，第二个text为定义的正则表达式。与substr功能相似，正则表达式出现多个并列的括号时，也全部处理。 返回值类型：text 示例： ``` SELECT regexp_substr('str','[ac]'); ---从“str”字符串中，匹配“ac”中的任意一个字符，结果为空 regexp_substr --------------- (1 row) ``` 示例：从混合文本中提取第一个手机号。 ``` SELECT regexp_substr('我的手机号是13812345678，备用号13987654321', '\d{11}'); -- \d{11}表示找11位连续数字 regexp_substr --------------- 13812345678 (1 row) ``` 示例：从快递单号中取前面10位订单号，10位后面的其它数字为时间戳，可以忽略。 ``` SELECT regexp_substr('FX58920235822025112404', '\d{10}'); --\d{10} 表示找10位连续数字，匹配过程，从左到右扫描字符串，取第一个符合“10位连续数字”的子串。 regexp_substr --------------- 5892023582 (1 row) ``` #### regexp_substr(source_char, pattern) 描述：正则表达式的抽取子串函数，source_char表示原始字符串，pattern表示定义的正则表达式。 返回值类型：varchar 示例： ``` --匹配【一个逗号】+【至少1个非逗号字符】+【一个逗号】的连续子串 SELECT regexp_substr('500 Hello World, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR"; REGEXPR_SUBSTR ------------------- , Redwood Shores, (1 row) ``` #### regexp_matches(string text, pattern text \[, flags text\]) 描述：返回string中所有匹配POSIX正则表达式的子字符串，返回结果是**文本数组**。如果pattern不匹配，该函数不返回行。这个函数在处理复杂文本解析时非常灵活，尤其适合需要多条件匹配或提取分组信息的场景。 - string为原始字符串。 - pattern为匹配模式。pattern可以是一个包含多个带圆括号的子表达式，例如**(bar)(beque)** ，匹配成功后返回对应的含有多个元素的文本数组，例如**{bar,beque}** ；如果pattern不包含带有圆括号的子表达式，则返回一个单一元素的文本数组，例如**{barbeque}**。 - flags为可选参数，取值为i表示大小写不敏感，g表示替换每一个匹配的子字符串而不仅仅是第一个，即全局匹配。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/notice_3.0-zh-cn.png) 如果提供了最后一个参数flags，但参数值是空字符串（''），且数据库SQL兼容模式设置为ORA的情况下，会导致返回结果为空集。这是因为ORA兼容模式将''作为NULL处理，避免此类行为的方式有如下几种： - 将数据库SQL兼容模式改为TD。 - 不提供最后一个参数，或最后一个参数不为空字符串。 返回值类型：多行文本数组 setof text\[\] ``` --匹配规则包含两个捕获分组，第一个(bar),匹配字面bar， 第二个(beque)，匹配字面beque，结果为包含2个元素的文本数组 SELECT regexp_matches('foobarbequebaz', '(bar)(beque)'); regexp_matches ---------------- {bar,beque} (1 row) --不带括号的匹配规则，结果为只包含1个元素的文本数组 SELECT regexp_matches('foobarbequebaz', 'barbeque'); regexp_matches ---------------- {barbeque} (1 row) --匹配b开头+若干非b字符，匹配结果分前后两组，即匹配到bar、beque ，但是不带g，所以只匹配了第一行 SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)'); regexp_matches -------------- {bar,beque} (1 row) --相比于上面的示例，带了g，则继续匹配，然后显示到第二行文本数组中 SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g'); regexp_matches -------------- {bar,beque} {bazil,barf} (2 rows) --从文本中提取所有11位手机号，全局匹配，返回所有行 SELECT regexp_matches('13812345678,13987654321', '\d{11}', 'g'); regexp_matches ---------------- {13812345678} {13987654321} (2 rows) --忽略大小写匹配，加参数i SELECT regexp_matches('fooBarBeQuebaz', 'barbeque', 'i'); regexp_matches ---------------- {BarBeQue} (1 row) --不带i，则区分大小写，匹配为空 SELECT regexp_matches('fooBarBeQuebaz', 'barbeque'); regexp_matches ---------------- (0 rows) ``` ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/caution_3.0-zh-cn.png) 如果没有子查询，当regexp_matches函数没有匹配上时，不会输出表中的数据，这通常不是所需的返回结果，应避免这种写法，建议可使用[regexp_substr(text,text)]函数来实现相同的功能。 ``` SELECT * FROM tab; c1 | c2 -----+----- dws | dws (1 row) SELECT c1, regexp_matches(c2, '(bar)(beque)') FROM tab; c1 | regexp_matches ----+---------------- (0 rows) SELECT c1, c2, (SELECT regexp_matches(c2, '(bar)(beque)')) FROM tab; c1 | c2 | regexp_matches -----+-----+---------------- dws | dws | (1 row) ```