字符串函数
更新时间：2022-09-13
PALO支持的字符串函数如下：
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_absentASCII
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
asciiCONCAT
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
concatCONCAT_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_wsENDS_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_withFIND_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_setGROUP_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_concatINSTR
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
instrLENGTH
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_lengthLOCATE
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
locateLOWER,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,lcaseLPAD
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
lpadLTRIM
Description
ltrim(string a)- 功能：将参数中从开始部分连续出现的空格去掉。
- 返回类型：string类型
Example
mysql> select ltrim(' today is friday');
+------------------------------+
| ltrim(' today is friday') |
+------------------------------+
| today is friday |
+------------------------------+Keywords
ltrimMONEY_FORMAT
Description
money_format(numric money)- 功能：转换为金钱格式
- 返回类型：string类型
Example
select money_format(11111);
+---------------------+
| money_format(11111) |
+---------------------+
| 11,111.00 |
+---------------------+Keywords
money_formatNULL_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_emptyPARSE_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_urlREGEXP_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, regexpREGEXP_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, regexpREPEAT
Description
repeat(string str, int n)- 功能：返回字符串str重复n次的结果
- 返回类型：string类型
Example
mysql> select repeat("abc", 3);
+------------------+
| repeat('abc', 3) |
+------------------+
| abcabcabc |
+------------------+Keywords
repeatREPLACE
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
replaceREVERSE
Description
reverse(string a)- 功能：将字符串反转
- 返回类型：string类型
Example
mysql> select reverse('doris');
+-----------------+
| reverse('doris') |
+-----------------+
| olap |
+-----------------+Keywords
reverseRPAD
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
rpadRTRIM
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
rtrimSPACE
Description
space(int n)- 功能：返回n个空格的字符串
- 返回类型：string类型
Example
mysql> select space(10);
+------------+
| space(10) |
+------------+
| |
+------------+
mysql> select space(20);
+----------------------+
| space(20) |
+----------------------+
| |
+----------------------+Keywords
spaceSPLIT_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_partSTARTS_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_withSTRLEFT,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,leftSTRRIGHT,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,rightSUBSTR,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,substringTRIM
Description
trim(string a)- 功能：将参数中右侧部分连续出现的空格和左侧部分连续出现的空格都去掉。该函数的效果和同时使用ltrim()和rtrim()的效果是一样的。
- 返回类型：string类型
Example
mysql> select trim(' today is friday ');
+--------------------------------+
| trim(' today is friday ') |
+--------------------------------+
| today is friday |
+--------------------------------+Keywords
trimUPPER,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