String Function
Last Updated：2021-04-13
Palo supports the following string functions:
1.ascii
2.concat
3.concat_ws
4.ends_with
5.find_in_set
6.group_concat
7.instr
8.length,char_length,character_length
9.locate
10.lower,lcase
11.lpad
12.ltrim
13.money_format
14.null_or_empty
15.parse_url
16.regexp_extract
17.regexp_replace
18.repeat
19.replace
20.reverse
21.rpad
22.rtrim
23.space
24.split_part
25.starts_with
26.strleft,left
27.strright,right
28.substr,substring
29.trim
30.upper,ucase
ASCII
Description
ascii(string str)- Function: return the ascii code corresponding to the first string in strings
- Return type: int type
Example
mysql> select ascii('palo');
+---------------+
| ascii('palo') |
+---------------+
| 112 |
+---------------+
mysql> select ascii('palo and doris');
+-------------------------+
| ascii('palo and doris') |
+-------------------------+
| 112 |
+-------------------------+Keywords
asciiCONCAT
Description
concat(string a, string b...)- Function: connect multiple strings together
- Return type: string type
- Instructions for use: concat () and concat_ws () combine multiple columns in a row into a new column, group_concat () is an aggregate function that combines the results of different rows into a new column
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...)- Function: connect the second parameter with parameters behind, the first parameter is the connector.
- Return type: string type
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)- Function: Judge if str ends with strEnd
- Return type: bool type
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)- Function: return the position of the first str in strlist (counting from 1). StrList separates multiple strings with commas. If no str is found in strList, then return 0.
- Return type: int type
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])- Function: the function is an aggregate function similar to sum (), in which, group_concat connects multiple rows of results in the result set into a string. The second parameter is the connector between strings, which can be omitted. The function is often used in combination with the group by statement.
- Return type: string type
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)- Function: return the location where the substr appears in str for the first time (counting from 1). If no substr appears in str, then return 0.
- Return type: int type
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)- Function: return the length of string. In which, return the length of byte in
length, and return the length of characters inchar(acter)_length. - Return type: int type
Example
mysql> select length('today');
+-----------------+
| length('today') |
+-----------------+
| 5 |
+-----------------+
mysql> select length("China");
+------------------+
| length('China') |
+------------------+
| 6 |
+------------------+
mysql> select char_length("China");
+-----------------------+
| char_length('China') |
+-----------------------+
| 2 |
+-----------------------+Notes: UTF-8 encoding, one Chinese character occupies 3 bytes.
Keywords
length, char_length, character_lengthLOCATE
Description
locate(string substr, string str[, int pos])- Function: Return the location where the substr appears in str (counting from 1). If the third parameter is specified, locate the substr in str string from where the string is subscripted with pos.
- Return type: int type
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)- Function: convert all strings in the parameter to lowercase
- Return type: string type
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)- Function: Return a string of length len (starting from the first letter) in str. If the len is greater than the length of str, then add pad characters before str until the length of the string reaches len. If len is smaller than the length of str, this function is equivalent to truncating str string and will return only the string with length len.
- Return type: string type
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)- Function: remove the spaces that appear continuously from the beginning of the parameter.
- Return type: string type
Example
mysql> select ltrim(' today is friday');
+------------------------------+
| ltrim(' today is friday') |
+------------------------------+
| today is friday |
+------------------------------+Keywords
ltrimMONEY_FORMAT
Description
money_format(numric money)- Function: convert to money format
- Return type: string type
Example
select money_format(11111);
+---------------------+
| money_format(11111) |
+---------------------+
| 11,111.00 |
+---------------------+Keywords
money_formatNULL_OR_EMPTY
Description
null_or_empty(string str)- Function: judge whether the str is NULL or empty string
- Return type: bool type
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)- Function: parse the field corresponding to name in url, there are following options of name: 'PROTOCOL', 'HOST', 'PATH', 'REF', 'AUTHORITY', 'FILE', 'USERINFO', 'PORT', 'QUERY', and then return the result.
- Return type: string type
Example
mysql> select parse_url ('https://cloud.baidu.com/product/palo.html', 'PROTOCOL');
+--------------------------------------------------------------------+
| parse_url('https://cloud.baidu.com/product/palo.html', 'PROTOCOL') |
+--------------------------------------------------------------------+
| https |
+--------------------------------------------------------------------+
1 row in set (0.02 sec)Keywords
parse_urlREGEXP_EXTRACT
Description
regexp_extract(string subject, string pattern, int index)- Function: regular matching the string. Return the entire string matched if the index is 0, return the first, second, ......th part accordingly when the index is 1, 2, .......
- Return type: string type
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)- Function: replace the part in the initial string that matches the pattern with replacement.
- Return type: string type
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)- Function: return the result after repeating string str n times
- Return type: string type
Example
mysql> select repeat("abc", 3);
+------------------+
| repeat('abc', 3) |
+------------------+
| abcabcabc |
+------------------+Keywords
repeatREPLACE
Description
replace(string oriStr, string src, string dest)- Function: replace all srcs in oriStr with dest, take the result as the return value. Note the difference between regexp_replace (), replace() is the string matched exactly, and regexp_replace () can support expressions.
- Return type: string type
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)- Function: reverse the string
- Return type: string type
Example
mysql> select reverse('palo');
+-----------------+
| reverse('palo') |
+-----------------+
| olap |
+-----------------+Keywords
reverseRPAD
Description
rpad(string str, int len, string pad)- Function: return a string of length len (starting from the first letter) in str. If the len is greater than the length of str, then add pad characters behind str until the length of the string reaches len. If len is smaller than the length of str, this function is equivalent to truncating str string and will return only the string with length len.
- Return type: string type
Example
mysql> select rpad("hello", 10, 'xy');
+-------------------------+
| rpad('hello', 10, 'xy') |
+-------------------------+
| helloxyxyx |
+-------------------------+Keywords
rpadRTRIM
Description
rtrim(string a)- Function: remove the spaces that appear continuously in the right part of the parameter. Compare with ltrim () to find the difference in function. One is removed with the space before the string, and the other is removed with the space after the string.
- Return type: string type
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)- Function: return a string of n spaces
- Return type: string type
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)- Function: divide the Str according to splitStr, and return the num value
- Return type: string type
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)- Function: Judge whether str starts with strPrefix
- Return type: bool type
Example
mysql> select starts_with('baidu palo','palo');
+-----------------------------------+
| starts_with('baidu palo', 'palo') |
+-----------------------------------+
| 0 |
+-----------------------------------+
mysql> select starts_with('baidu palo','baidu');
+------------------------------------+
| starts_with('baidu palo', 'baidu') |
+------------------------------------+
| 1 |
+------------------------------------+Keywords
starts_withSTRLEFT,LEFT
Description
strleft(string a, int num_chars)
left(string a, int num_chars)- Function: return the leftmost num_chars characters in a string.
- Return type: string type
Example
mysql> select strleft('palo@baidu',5);
+--------------------------+
| strleft('palo@baidu', 5) |
+--------------------------+
| palo@ |
+--------------------------+
mysql> select left('palo@baidu',4);
+-----------------------+
| left('palo@baidu', 4) |
+-----------------------+
| palo |
+-----------------------+Keywords
strleft,leftSTRRIGHT,RIGHT
Description
strright(string a, int num_chars)
right(string a, int num_chars)- Function: return the rightmost num_chars characters in a string.
- Return type: string type
Example
mysql> select strright('palo@baidu',5);
+---------------------------+
| strright('palo@baidu', 5) |
+---------------------------+
| baidu |
+---------------------------+
mysql> select right('palo@baidu',6);
+------------------------+
| right('palo@baidu', 6) |
+------------------------+
| @baidu |
+------------------------+Keywords
strright,rightSUBSTR,SUBSTRING
Description
substr(string a, int start [, int len])
substring(string a, int start[, int len])- Function: it is a function for getting substring, which returns the partial string with length len from start in the string described by the first parameter. The first letter is subscripted with 1.
- Return type: string type
Example
mysql> select substring('baidupalo',6);
+---------------------------+
| substring('baidupalo', 6) |
+---------------------------+
| palo |
+---------------------------+Keywords
substr,substringTRIM
Description
trim(string a)- Function: remove the continuous spaces in the right part and the continuous spaces in the left part of the parameter. The function has the same effect with that of using ltrim () and rtrim () at the same time.
- Return type: string type
Example
mysql> select trim(' today is friday ');
+--------------------------------+
| trim(' today is friday ') |
+--------------------------------+
| today is friday |
+--------------------------------+Keywords
trimUPPER,UCASE
Description
upper(string a)
ucase(string a)- Function: convert all letters in a string to uppercase.
- Return type: string type
Example
mysql> select upper('toDAY Is FridAy');
+--------------------------+
| upper('toDAY Is FridAy') |
+--------------------------+
| TODAY IS FRIDAY |
+--------------------------+
mysql> select ucase('palo');
+---------------+
| ucase('palo') |
+---------------+
| PALO |
+---------------+Keywords
upper, ucase