字符串函数

更新时间：2021-08-20

Doris支持的字符串函数如下：

1.append_trailing_char_if_absent
2.ascii
3.concat
4.concat_ws
5.ends_with
6.find_in_set
7.group_concat
8.instr
9.length,char_length,character_length
10.locate
11.lower,lcase
12.lpad
13.ltrim
14.money_format
15.null_or_empty
16.parse_url
17.regexp_extract
18.regexp_replace
19.repeat
20.replace
21.reverse
22.rpad
23.rtrim
24.space
25.split_part
26.starts_with
27.strleft,left
28.strright,right
29.substr,substring
30.trim
31.upper,ucase

APPEND_TRAILING_CHAR_IF_ABSENT

Description

append_trailing_char_if_absent(VARCHAR str, VARCHAR trailing_char)

功能：如果str字符串非空并且末尾不包含trailing_char字符，则将trailing_char字符附加到末尾。 trailing_char只包含一个字符，如果包含多个字符，将返回NULL
返回类型：字符串类型

Example

mysql> select append_trailing_char_if_absent('abcde', 'a');
+----------------------------------------------+
| append_trailing_char_if_absent('abcde', 'a') |
+----------------------------------------------+
| abcdea                                       |
+----------------------------------------------+

mysql> select append_trailing_char_if_absent('abcde', 'ab');
+-----------------------------------------------+
| append_trailing_char_if_absent('abcde', 'ab') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+

Keywords

append_trailing_char_if_absent

ASCII

Description

ascii(string str)

功能：返回字符串第一个字符串对应的ascii 码
返回类型：int类型

Example

mysql> select ascii('doris');
+---------------+
| ascii('doris') |
+---------------+
|           112 |
+---------------+

mysql> select ascii('doris and doris');
+-------------------------+
| ascii('doris and doris') |
+-------------------------+
|                     112 |
+-------------------------+

Keywords

ascii

CONCAT

Description

concat(string a, string b...)

功能：将多个字符串连接起来
返回类型：string类型
使用说明：concat()和concat_ws()都是将一行中的多个列合成1个新的列，group_concat()是聚合函数，将不同行的结果合成1个新的列

Example

mysql> select concat('The date today is ',to_date(now()));
+----------------------------------------------+
| concat('The date today is ', to_date(now())) |
+----------------------------------------------+
| The date today is 2020-12-29                 |
+----------------------------------------------+

Keywords

concat

CONCAT_WS

Description

concat_ws(string sep, string a, string b...)

功能：将第二个参数以及后面的参数连接起来，连接符为第一个参数。
返回类型：string类型

Example

mysql> select concat_ws('a', 'b', 'c', 'd');
+-------------------------------+
| concat_ws('a', 'b', 'c', 'd') |
+-------------------------------+
| bacad                         |
+-------------------------------+

Keywords

concat_ws

ENDS_WITH

Description

ends_with(string str, string strEnd)

功能：判断str是否以strEnd结尾
返回类型：bool类型

Example

mysql> select ends_with('today','y');
+-------------------------+
| ends_with('today', 'y') |
+-------------------------+
|                       1 |
+-------------------------+

Keywords

ends_with

FIND_IN_SET

Description

find_in_set(string str, string strList)

功能：返回strlist中出现第一次str的位置（从1开始计数）。strList用逗号分隔多个字符串。如果在strList中没有找到str，则返回0。
返回类型：int类型

Example

mysql> select find_in_set("beijing", "tianji,beijing,shanghai");
+---------------------------------------------------+
| find_in_set('beijing', 'tianji,beijing,shanghai') |
+---------------------------------------------------+
|                                                 2 |
+---------------------------------------------------+

Keywords

find_in_set

GROUP_CONCAT

Description

group_concat(string s [, string sep])

功能：该函数是类似于sum()的聚合函数，group_concat将结果集中的多行结果连接成一个字符串。第二个参数为字符串之间的连接符号，该参数可以省略。该函数通常需要和group by 语句一起使用。
返回类型：string类型

Example

mysql> select k1, group_concat(k2) from tbl group by k1;
+----+------------------+
| k1 | group_concat(k2) |
+-----------------------+
| 1  | 1,2,3,4          |
+-----------------------+
| 1  | 5,6,7,8          |
+-----------------------+

Keywords

group_concat

INSTR

Description

instr(string str, string substr)

功能：返回substr在str中第一次出现的位置（从1开始计数）。如果substr不在str中出现，则返回0。
返回类型：int类型

Example

mysql> select instr('foo bar bletch', 'b');
+------------------------------+
| instr('foo bar bletch', 'b') |
+------------------------------+
|                            5 |
+------------------------------+

mysql> select instr('foo bar bletch', 'z');
+------------------------------+
| instr('foo bar bletch', 'z') |
+------------------------------+
|                            0 |
+------------------------------+

Keywords

instr

LENGTH

Description

length(string a)

char_length(string a)

character_length(string a)

功能：返回字符串的长度。其中 length 返回字节长度，而 char(acter)_length 返回字符长度。
返回类型：int类型

Example

mysql> select length('today');
+-----------------+
| length('today') |
+-----------------+
|               5 |
+-----------------+

mysql> select length("中国");
+------------------+
| length('中国')   |
+------------------+
|                6 |
+------------------+

mysql> select char_length("中国");
+-----------------------+
| char_length('中国')   |
+-----------------------+
|                     2 |
+-----------------------+

注：UTF-8 编码，一个汉字占 3 个字节。

Keywords

length, char_length, character_length

LOCATE

Description

locate(string substr, string str[, int pos])

功能：返回substr在str中出现的位置（从1开始计数）。如果指定第3个参数，则从str以pos下标开始的字符串处开始查找substr出现的位置。
返回类型：int类型

Example

mysql> select locate('bj', 'where is bj', 10);
+---------------------------------+
| locate('bj', 'where is bj', 10) |
+---------------------------------+
|                              10 |
+---------------------------------+

mysql> select locate('bj', 'where is bj', 11);
+---------------------------------+
| locate('bj', 'where is bj', 11) |
+---------------------------------+
|                               0 |
+---------------------------------+

Keywords

locate

LOWER,LCASE

Description

lower(string a)

lcase(string a)

功能：将参数中所有的字符串都转换成小写
返回类型：string类型

Example

mysql> select lower('toDAY Is FridAy');
+--------------------------+
| lower('toDAY Is FridAy') |
+--------------------------+
| today is friday          |
+--------------------------+

mysql> select lcase('toDAY Is FridAy');
+--------------------------+
| lcase('toDAY Is FridAy') |
+--------------------------+
| today is friday          |
+--------------------------+

Keywords

lower,lcase

LPAD

Description

lpad(string str, int len, string pad)

功能：返回str中长度为len（从首字母开始算起）的字符串。如果len大于str的长度，则在str的前面不断补充pad字符，直到该字符串的长度达到len为止。如果len小于str的长度，该函数相当于截断str字符串，只返回长度为len的字符串。
返回类型：string类型

Example

 mysql> select lpad('aoaoaoao',10,'xy');
+----------------------------+
| lpad('aoaoaoao', 10, 'xy') |
+----------------------------+
| xyaoaoaoao                 |
+----------------------------+

mysql> select lpad('aoaoaoao',6,'xy');
+---------------------------+
| lpad('aoaoaoao', 6, 'xy') |
+---------------------------+
| aoaoao                    |
+---------------------------+

Keywords

lpad

LTRIM

Description

ltrim(string a)

功能：将参数中从开始部分连续出现的空格去掉。
返回类型：string类型

Example

mysql> select ltrim('    today is friday');
+------------------------------+
| ltrim('    today is friday') |
+------------------------------+
| today is friday              |
+------------------------------+

Keywords

ltrim

MONEY_FORMAT

Description

money_format(numric money)

功能：转换为金钱格式
返回类型：string类型

Example

select money_format(11111);
+---------------------+
| money_format(11111) |
+---------------------+
| 11,111.00           |
+---------------------+

Keywords

money_format

NULL_OR_EMPTY

Description

null_or_empty(string str)

功能：判断str是否为NULL或空字符串
返回类型：bool类型

Example

mysql> select null_or_empty('');
+-------------------+
| null_or_empty('') |
+-------------------+
|                 1 |
+-------------------+

mysql> select null_or_empty('today');
+------------------------+
| null_or_empty('today') |
+------------------------+
|                      0 |
+------------------------+

Keywords

null_or_empty

PARSE_URL

Description

parse_url(string url, string name)

功能：在url解析出name对应的字段，name可选项为：'PROTOCOL', 'HOST', 'PATH', 'REF', 'AUTHORITY', 'FILE', 'USERINFO', 'PORT', 'QUERY'，将结果返回。
返回类型：string类型

Example

mysql> select parse_url ('https://cloud.baidu.com/product/doris.html', 'PROTOCOL');
+--------------------------------------------------------------------+
| parse_url('https://cloud.baidu.com/product/doris.html', 'PROTOCOL') |
+--------------------------------------------------------------------+
| https                                                              |
+--------------------------------------------------------------------+
1 row in set (0.02 sec)

Keywords

parse_url

REGEXP_EXTRACT

Description

regexp_extract(string subject, string pattern, int index)

功能：字符串正则匹配。index为0返回整个匹配的字符串，index为1，2，……，返回第一，第二，……部分。
返回类型：string类型

Example

mysql> select regexp_extract('AbcdBCdefGHI','.*?([[:lower:]]+)',1);
+--------------------------------------------------------+
| regexp_extract('AbcdBCdefGHI', '.*?([[:lower:]]+)', 1) |
+--------------------------------------------------------+
| def                                                    |
+--------------------------------------------------------+

mysql> select regexp_extract('AbcdBCdefGHI','.*?([[:lower:]]+).*?',1);
+-----------------------------------------------------------+
| regexp_extract('AbcdBCdefGHI', '.*?([[:lower:]]+).*?', 1) |
+-----------------------------------------------------------+
| bcd                                                       |
+-----------------------------------------------------------+

Keywords

regexp_extract, regexp

REGEXP_REPLACE

Description

regexp_replace(string initial, string pattern, string replacement)

功能：用replacement替换initial字符串中匹配pattern的部分。
返回类型：string类型

Example

mysql> select regexp_replace('aaabbbaaa','b+','xyz');
+------------------------------------------+
| regexp_replace('aaabbbaaa', 'b+', 'xyz') |
+------------------------------------------+
| aaaxyzaaa                                |
+------------------------------------------+

mysql> select regexp_replace('aaabbbaaa','(b+)','<\\1>');
+---------------------------------------------+
| regexp_replace('aaabbbaaa', '(b+)', '<\1>') |
+---------------------------------------------+
| aaa<bbb>aaa                                 |
+---------------------------------------------+

mysql> select regexp_replace('123-456-789','[^[:digit:]]','');
+---------------------------------------------------+
| regexp_replace('123-456-789', '[^[:digit:]]', '') |
+---------------------------------------------------+
| 123456789                                         |
+---------------------------------------------------+

Keywords

regexp_replace, regexp

REPEAT

Description

repeat(string str, int n)

功能：返回字符串str重复n次的结果
返回类型：string类型

Example

mysql> select repeat("abc", 3);
+------------------+
| repeat('abc', 3) |
+------------------+
| abcabcabc        |
+------------------+

Keywords

repeat

REPLACE

Description

replace(string oriStr, string src, string dest)

功能：oriStr中所有的src替换成dest，结果作为返回值。注意和regexp_replace()的区别，replace()是完全匹配字符串，regexp_replace()可支持表达式。
返回类型：string类型

Example

mysql> select replace('aaabbbaaa','b+','xyz');
+-----------------------------------+
| replace('aaabbbaaa', 'b+', 'xyz') |
+-----------------------------------+
| aaabbbaaa                         |
+-----------------------------------+

mysql> select replace('aaabbbaaa','bb','xyz');
+-----------------------------------+
| replace('aaabbbaaa', 'bb', 'xyz') |
+-----------------------------------+
| aaaxyzbaaa                        |
+-----------------------------------+

Keywords

replace

REVERSE

Description

reverse(string a)

功能：将字符串反转
返回类型：string类型

Example

mysql> select reverse('doris');
+-----------------+
| reverse('doris') |
+-----------------+
| olap            |
+-----------------+

Keywords

reverse

RPAD

Description

rpad(string str, int len, string pad)

功能：返回str中长度为len（从首字母开始算起）的字符串。如果len大于str的长度，则在str 的后面不断补充pad字符，直到该字符串的长度达到len 为止。如果len小于str的长度，该函数相当于截断str字符串，只返回长度为len的字符串。
返回类型：string类型

Example

mysql> select rpad("hello", 10, 'xy');  
+-------------------------+
| rpad('hello', 10, 'xy') |
+-------------------------+
| helloxyxyx              |
+-------------------------+

Keywords

rpad

RTRIM

Description

rtrim(string a)

功能：将参数中从右侧部分部分连续出现的空格去掉。可以和ltrim()对比看出功能的不同之处。一个是去掉字符串前面的空格，一个是去掉字符串后面的空格。
返回类型：string类型

Example

mysql> select rtrim('    today is friday   ');
+---------------------------------+
| rtrim('    today is friday   ') |
+---------------------------------+
|     today is friday             |
+---------------------------------+

mysql> select ltrim('    today is friday   ');
+---------------------------------+
| ltrim('    today is friday   ') |
+---------------------------------+
| today is friday                 |
+---------------------------------+

Keywords

rtrim

SPACE

Description

space(int n)

功能：返回n个空格的字符串
返回类型：string类型

Example

mysql> select space(10);
+------------+
| space(10)  |
+------------+
|            |
+------------+

mysql> select space(20);
+----------------------+
| space(20)            |
+----------------------+
|                      |
+----------------------+

Keywords

space

SPLIT_PART

Description

split_part(string str, string splitStr, int num)

功能：str按照splitStr进行切分，返回第num个值
返回类型：string类型

Example

select split_part('12,31,45,232', ',', 3);
+------------------------------------+
| split_part('12,31,45,232', ',', 3) |
+------------------------------------+
| 45                                 |
+------------------------------------+

Keywords

split_part

STARTS_WITH

Description

starts_with(string str, string strPrefix)

功能：判断str是否以strPrefix开始
返回类型：bool类型

Example

mysql> select starts_with('baidu doris','doris');
+-----------------------------------+
| starts_with('baidu doris', 'doris') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

mysql> select starts_with('baidu doris','baidu');
+------------------------------------+
| starts_with('baidu doris', 'baidu') |
+------------------------------------+
|                                  1 |
+------------------------------------+

Keywords

starts_with

STRLEFT,LEFT

Description

strleft(string a, int num_chars)

left(string a, int num_chars)

功能：返回字符串中最左边的num_chars个字符。
返回类型：string类型

Example

mysql> select strleft('doris@baidu',5);
+--------------------------+
| strleft('doris@baidu', 5) |
+--------------------------+
| doris@                    |
+--------------------------+

mysql> select left('doris@baidu',4);
+-----------------------+
| left('doris@baidu', 4) |
+-----------------------+
| doris                  |
+-----------------------+

Keywords

strleft,left

STRRIGHT,RIGHT

Description

strright(string a, int num_chars)

right(string a, int num_chars)

功能：返回字符串中最右边的num_chars个字符。
返回类型：string类型

Example

mysql> select strright('doris@baidu',5);
+---------------------------+
| strright('doris@baidu', 5) |
+---------------------------+
| baidu                     |
+---------------------------+

mysql> select right('doris@baidu',6);
+------------------------+
| right('doris@baidu', 6) |
+------------------------+
| @baidu                 |
+------------------------+

Keywords

strright,right

SUBSTR,SUBSTRING

Description

substr(string a, int start [, int len])

substring(string a, int start[, int len])

功能：求子串函数，返回第一个参数描述的字符串中从start开始长度为len的部分字符串。首字母的下标为1。
返回类型：string类型

Example

mysql> select substring('baidudoris',6);
+---------------------------+
| substring('baidudoris', 6) |
+---------------------------+
| doris                      |
+---------------------------+

Keywords

substr,substring

TRIM

Description

trim(string a)

功能：将参数中右侧部分连续出现的空格和左侧部分连续出现的空格都去掉。该函数的效果和同时使用ltrim()和rtrim()的效果是一样的。
返回类型：string类型

Example

mysql> select trim('    today is friday   ');
+--------------------------------+
| trim('    today is friday   ') |
+--------------------------------+
| today is friday                |
+--------------------------------+

Keywords

trim

UPPER,UCASE

Description

upper(string a)

ucase(string a)

功能：将字符串所有字母都转换成大写。
返回类型：string类型

Example

mysql> select upper('toDAY Is FridAy');
+--------------------------+
| upper('toDAY Is FridAy') |
+--------------------------+
| TODAY IS FRIDAY          |
+--------------------------+

mysql> select ucase('doris');
+---------------+
| ucase('doris') |
+---------------+
| DORIS          |
+---------------+

Keywords

upper, ucase

位操作函数

条件函数

Palo文档 PALO

PALO

字符串函数

APPEND_TRAILING_CHAR_IF_ABSENT

Description

Example

Keywords

ASCII

Description

Example

Keywords

CONCAT

Description

Example

Keywords

CONCAT_WS

Description

Example

Keywords

ENDS_WITH

Description

Example

Keywords

FIND_IN_SET

Description

Example

Keywords

GROUP_CONCAT

Description

Example

Keywords

INSTR

Description

Example

Keywords

LENGTH

Description

Example

Keywords

LOCATE

Description

Example

Keywords

LOWER,LCASE

Description

Example

Keywords

LPAD

Description

Example

Keywords

LTRIM

Description

Example

Keywords

MONEY_FORMAT

Description

Example

Keywords

NULL_OR_EMPTY

Description

Example

Keywords

PARSE_URL

Description

Example

Keywords

REGEXP_EXTRACT

Description

Example

Keywords

REGEXP_REPLACE

Description

Example

Keywords

REPEAT

Description

Example

Keywords

REPLACE