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

ascii

CONCAT

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

concat

CONCAT_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_ws

ENDS_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_with

FIND_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_set

GROUP_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_concat

INSTR

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

instr

LENGTH

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 in char(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_length

LOCATE

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

locate

LOWER,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,lcase

LPAD

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

lpad

LTRIM

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

ltrim

MONEY_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_format

NULL_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_empty

PARSE_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_url

REGEXP_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, regexp

REGEXP_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, regexp

REPEAT

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

repeat

REPLACE

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

replace

REVERSE

Description

reverse(string a)

Function: reverse the string
Return type: string type

Example

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

Keywords

reverse

RPAD

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

rpad

RTRIM

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

rtrim

SPACE

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

space

SPLIT_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_part

STARTS_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_with

STRLEFT,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,left

STRRIGHT,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,right

SUBSTR,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,substring

TRIM

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

trim

UPPER,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

Operation Guide

Conditional Function

Data Warehouse

String Function

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

Description

Example

Keywords

REVERSE

Description