字符串函数
适用场景
- 数据清洗:通过TRIM去除空格,通过UPPER/LOWER统一大小写。
- 字符串拼接:通过CONCAT或CONCAT_WS拼接多个字段。
- 数据脱敏:通过SUBSTRING和REPLACE实现字段脱敏处理。
- 模式替换:通过REGEXP_REPLACE进行正则替换。
- 格式化输出:通过LPAD/RPAD对齐字段,通过FORMAT_NUMBER格式化数字。
- 模式匹配:通过LIKE进行简单模式匹配,通过REGEXP进行正则匹配。
约束与限制
- CONCAT中任一参数为NULL时结果为NULL,CONCAT_WS会跳过NULL参数。
- SUBSTRING的起始位置从1开始,0与1等价。
- REGEXP_REPLACE使用Java正则语法。
- TRIM指定string1时仅去除与string1完全匹配的单字符,非子串删除。
- 当len小于string原始长度时,LPAD/RPAD会截断字符串到len长度。
语法结构
| 语法 | 返回类型 | 说明 | 示例 |
|---|---|---|---|
| string1 || string2 | STRING | 字符串拼接。将string1和string2首尾连接为一个新字符串;string1和string2均须为STRING类型;若任一操作数为NULL则返回NULL。 | SELECT 'hello' \|\| ' ' \|\| 'world'; -- 'hello world' |
| CHAR_LENGTH(string) | INT | 返回字符串的字符数。string须为STRING类型;与CHARACTER_LENGTH完全等价;多字节字符(如中文)按1个字符计数;若string为NULL则返回NULL。 | SELECT CHAR_LENGTH('hello'); -- 5 |
| CHARACTER_LENGTH(string) | INT | 返回字符串的字符数。与CHAR_LENGTH完全等价,为SQL标准别名;string须为STRING类型;若string为NULL则返回NULL。 | SELECT CHARACTER_LENGTH('你好'); -- 2 |
| UPPER(string) | STRING | 转换为大写。将string中所有小写字母转换为大写字母,非字母字符保持不变;string须为STRING类型;若string为NULL则返回NULL。 | SELECT UPPER('hello'); -- 'HELLO' |
| LOWER(string) | STRING | 转换为小写。将string中所有大写字母转换为小写字母,非字母字符保持不变;string须为STRING类型;若string为NULL则返回NULL。 | SELECT LOWER('HELLO'); -- 'hello' |
| INITCAP(string) | STRING | 首字母大写。将string中每个单词的首字母转换为大写,其余字母转换为小写;单词以非字母字符分隔;string须为STRING类型;若string为NULL则返回NULL。 | SELECT INITCAP('hello world'); -- 'Hello World' |
| TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) | STRING | 去除指定字符。从string2的首部(LEADING)、尾部(TRAILING)或两端(BOTH,默认)去除与string1匹配的字符;省略string1和BOTH时默认去除两端空格;string1和string2均须为STRING类型;若任一参数为NULL则返回NULL。 | SELECT TRIM(BOTH 'x' FROM 'xxhelloxx'); -- 'hello' |
| LTRIM(string) | STRING | 去除前导空格。移除string左侧所有空格字符;string须为STRING类型;若string为NULL则返回NULL。 | SELECT LTRIM(' hello'); -- 'hello' |
| RTRIM(string) | STRING | 去除尾部空格。移除string右侧所有空格字符;string须为STRING类型;若string为NULL则返回NULL。 | SELECT RTRIM('hello '); -- 'hello' |
| SUBSTRING(string FROM pos [FOR len]) | STRING | 截取子串(SQL标准语法)。从string的pos位置开始截取长度为len的子串;pos从1开始,0与1等价;省略FOR len时截取到字符串末尾;pos为负数时从末尾倒数;string须为STRING类型,pos须为INT类型;若string为NULL则返回NULL。 | SELECT SUBSTRING('hello world' FROM 1 FOR 5); -- 'hello' |
| SUBSTRING(string, pos [, len]) | STRING | 截取子串(逗号语法)。与SUBSTRING(string FROM pos [FOR len])语义完全等价,仅语法形式不同;pos从1开始,0与1等价;省略len时截取到字符串末尾;string须为STRING类型,pos须为INT类型;若string为NULL则返回NULL。 | SELECT SUBSTRING('hello world', 7); -- 'world' |
| CONCAT(string1, string2, ...) | STRING | 拼接字符串。将所有参数按顺序连接为一个字符串;所有参数均须为STRING类型;支持两个及以上参数;与||运算符的关键区别:任一参数为NULL时整个结果为NULL。 | SELECT CONCAT('hello', ' ', 'world'); -- 'hello world' |
| CONCAT_WS(separator, string1, ...) | STRING | 用分隔符拼接。使用separator作为分隔符连接所有非NULL的string参数;separator和string参数均须为STRING类型;与CONCAT的关键区别:跳过NULL参数而非返回NULL;若separator为NULL则返回NULL。 | SELECT CONCAT_WS('-', '2024', '01', '15'); -- '2024-01-15' |
| REPLACE(string, old, new) | STRING | 替换子串。将string中所有出现的old子串替换为new;区分大小写;old为空字符串时不做替换;string、old、new均须为STRING类型;若任一参数为NULL则返回NULL。 | SELECT REPLACE('hello world', 'world', 'Flink'); -- 'hello Flink' |
| REGEXP_REPLACE(string, regex, replacement) | STRING | 正则替换。使用Java正则语法将string中所有匹配regex的子串替换为replacement;replacement中可使用1、1、2等引用捕获组;string、regex、replacement均须为STRING类型;若任一参数为NULL则返回NULL。 | SELECT REGEXP_REPLACE('abc123', '[0-9]', ''); -- 'abc**' |
| REGEXP_EXTRACT(string, regex [, group]) | STRING | 正则提取。从string中提取匹配regex的子串;group指定返回的捕获组编号,默认为0(返回整个匹配);string和regex须为STRING类型,group须为INT类型;若无匹配则返回NULL;若任一参数为NULL则返回NULL。 | SELECT REGEXP_EXTRACT('abc123def', '([a-z]+)([0-9]+)', 2); -- '123' |
| POSITION(string1 IN string2) | INT | 查找位置。返回string1在string2中首次出现的起始位置,从1开始计数;若未找到则返回0;string1和string2均须为STRING类型;若任一参数为NULL则返回NULL。 | SELECT POSITION('world' IN 'hello world'); -- 7 |
| LPAD(string, len, pad) | STRING | 左侧填充。在string左侧填充pad字符串直到总长度达到len;若string长度已超过len则从右侧截断至len长度;string和pad须为STRING类型,len须为INT类型;若任一参数为NULL则返回NULL。 | SELECT LPAD('5', 3, '0'); -- '005' |
| RPAD(string, len, pad) | STRING | 右侧填充。在string右侧填充pad字符串直到总长度达到len;若string长度已超过len则从右侧截断至len长度;string和pad须为STRING类型,len须为INT类型;若任一参数为NULL则返回NULL。 | SELECT RPAD('5', 3, '0'); -- '500' |
| OVERLAY(string1 PLACING string2 FROM pos [FOR len]) | STRING | 覆盖子串。从string1的pos位置开始,用string2覆盖len个字符;pos从1开始;省略FOR len时覆盖长度为string2的长度;string1和string2须为STRING类型,pos和len须为INT类型;若任一参数为NULL则返回NULL。 | SELECT OVERLAY('hello world' PLACING 'FLINK' FROM 7); -- 'hello FLINK' |
| REVERSE(string) | STRING | 反转字符串。将string中字符顺序完全反转;string须为STRING类型;若string为NULL则返回NULL。 | SELECT REVERSE('hello'); -- 'olleh' |
| REPEAT(string, integer) | STRING | 重复字符串。将string重复integer次后连接为一个新字符串;string须为STRING类型,integer须为INT类型;integer为0时返回空字符串,integer为负数时返回NULL;若string为NULL则返回NULL。 | SELECT REPEAT('ab', 3); -- 'ababab' |
| SPACE(integer) | STRING | 生成空格字符串。返回由integer个空格字符组成的字符串;integer须为INT类型;integer为0时返回空字符串,integer为负数时返回NULL。 | SELECT SPACE(3); -- ' ' |
| LEFT(string, integer) | STRING | 左侧截取。返回string最左侧的integer个字符;string须为STRING类型,integer须为INT类型;integer为0时返回空字符串,integer为负数时返回NULL;若string为NULL则返回NULL。 | SELECT LEFT('hello world', 5); -- 'hello' |
| RIGHT(string, integer) | STRING | 右侧截取。返回string最右侧的integer个字符;string须为STRING类型,integer须为INT类型;integer为0时返回空字符串,integer为负数时返回NULL;若string为NULL则返回NULL。 | SELECT RIGHT('hello world', 5); -- 'world' |
| FORMAT_NUMBER(numeric, decimal_places) | STRING | 格式化数字。将numeric格式化为带千分位分隔符的字符串,保留decimal_places位小数;numeric须为数值类型,decimal_places须为INT类型;若任一参数为NULL则返回NULL。 | SELECT FORMAT_NUMBER(12345.6789, 2); -- '12,345.68' |
| LIKE(string1, string2) | BOOLEAN | 模式匹配。判断string1是否匹配模式string2;%匹配任意字符序列(包括空序列),_匹配恰好一个字符;如需匹配通配符本身需使用ESCAPE子句转义;string1和string2均须为STRING类型;若任一参数为NULL则返回NULL。 | SELECT LIKE('hello', 'h%'); -- TRUE |
| REGEXP(string1, string2) | BOOLEAN | 正则匹配。判断string1是否匹配正则表达式string2;使用Java正则语法;string1和string2均须为STRING类型;若任一参数为NULL则返回NULL。 | SELECT REGEXP('abc123', '[a-z]+[0-9]+'); -- TRUE |
| URL_DECODE | VARCHAR | URL_DECODE函数用于将application/x-www-form-urlencoded MIME格式的输入字符串转换为标准字符串。 | 详情请参见URL_DECODE。 |
| URL_ENCODE | VARCHAR | URL_ENCODE用于以application/x-www-form-urlencoded MIME格式对输入字符串进行编码并返回编码后的字符串。 | 详情请参见URL_ENCODE。 |