字符串函数

1append_trailing_char_if_absent(VARCHAR str, VARCHAR trailing_char)

1mysql> select append_trailing_char_if_absent('abcde', 'a');
2+----------------------------------------------+
3| append_trailing_char_if_absent('abcde', 'a') |
4+----------------------------------------------+
5| abcdea                                       |
6+----------------------------------------------+
7
8mysql> select append_trailing_char_if_absent('abcde', 'ab');
9+-----------------------------------------------+
10| append_trailing_char_if_absent('abcde', 'ab') |
11+-----------------------------------------------+
12| NULL                                          |
13+-----------------------------------------------+

1append_trailing_char_if_absent

1ascii(string str)

1mysql> select ascii('doris');
2+---------------+
3| ascii('doris') |
4+---------------+
5|           112 |
6+---------------+
7
8mysql> select ascii('doris and doris');
9+-------------------------+
10| ascii('doris and doris') |
11+-------------------------+
12|                     112 |
13+-------------------------+

1ascii

1concat(string a, string b...)

1mysql> select concat('The date today is ',to_date(now()));
2+----------------------------------------------+
3| concat('The date today is ', to_date(now())) |
4+----------------------------------------------+
5| The date today is 2020-12-29                 |
6+----------------------------------------------+

1concat

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

1mysql> select concat_ws('a', 'b', 'c', 'd');
2+-------------------------------+
3| concat_ws('a', 'b', 'c', 'd') |
4+-------------------------------+
5| bacad                         |
6+-------------------------------+

1concat_ws

1ends_with(string str, string strEnd)

1mysql> select ends_with('today','y');
2+-------------------------+
3| ends_with('today', 'y') |
4+-------------------------+
5|                       1 |
6+-------------------------+

1ends_with

1find_in_set(string str, string strList)

1mysql> select find_in_set("beijing", "tianji,beijing,shanghai");
2+---------------------------------------------------+
3| find_in_set('beijing', 'tianji,beijing,shanghai') |
4+---------------------------------------------------+
5|                                                 2 |
6+---------------------------------------------------+

1find_in_set

1group_concat(string s [, string sep])

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

1group_concat

1instr(string str, string substr)

1mysql> select instr('foo bar bletch', 'b');
2+------------------------------+
3| instr('foo bar bletch', 'b') |
4+------------------------------+
5|                            5 |
6+------------------------------+
7
8mysql> select instr('foo bar bletch', 'z');
9+------------------------------+
10| instr('foo bar bletch', 'z') |
11+------------------------------+
12|                            0 |
13+------------------------------+

1instr

1length(string a)
2
3char_length(string a)
4
5character_length(string a)

1mysql> select length('today');
2+-----------------+
3| length('today') |
4+-----------------+
5|               5 |
6+-----------------+
7
8mysql> select length("中国");
9+------------------+
10| length('中国')   |
11+------------------+
12|                6 |
13+------------------+
14
15mysql> select char_length("中国");
16+-----------------------+
17| char_length('中国')   |
18+-----------------------+
19|                     2 |
20+-----------------------+

1length, char_length, character_length

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

1mysql> select locate('bj', 'where is bj', 10);
2+---------------------------------+
3| locate('bj', 'where is bj', 10) |
4+---------------------------------+
5|                              10 |
6+---------------------------------+
7
8mysql> select locate('bj', 'where is bj', 11);
9+---------------------------------+
10| locate('bj', 'where is bj', 11) |
11+---------------------------------+
12|                               0 |
13+---------------------------------+

1locate

1lower(string a)
2
3lcase(string a)

1mysql> select lower('toDAY Is FridAy');
2+--------------------------+
3| lower('toDAY Is FridAy') |
4+--------------------------+
5| today is friday          |
6+--------------------------+
7
8mysql> select lcase('toDAY Is FridAy');
9+--------------------------+
10| lcase('toDAY Is FridAy') |
11+--------------------------+
12| today is friday          |
13+--------------------------+

1lower,lcase

1lpad(string str, int len, string pad)

1 mysql> select lpad('aoaoaoao',10,'xy');
2+----------------------------+
3| lpad('aoaoaoao', 10, 'xy') |
4+----------------------------+
5| xyaoaoaoao                 |
6+----------------------------+
7
8mysql> select lpad('aoaoaoao',6,'xy');
9+---------------------------+
10| lpad('aoaoaoao', 6, 'xy') |
11+---------------------------+
12| aoaoao                    |
13+---------------------------+

1lpad

1ltrim(string a)

1mysql> select ltrim('    today is friday');
2+------------------------------+
3| ltrim('    today is friday') |
4+------------------------------+
5| today is friday              |
6+------------------------------+

1ltrim

1money_format(numric money)

1select money_format(11111);
2+---------------------+
3| money_format(11111) |
4+---------------------+
5| 11,111.00           |
6+---------------------+

1money_format

1null_or_empty(string str)

1mysql> select null_or_empty('');
2+-------------------+
3| null_or_empty('') |
4+-------------------+
5|                 1 |
6+-------------------+
7
8mysql> select null_or_empty('today');
9+------------------------+
10| null_or_empty('today') |
11+------------------------+
12|                      0 |
13+------------------------+

1null_or_empty

1parse_url(string url, string name)

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

1parse_url

1regexp_extract(string subject, string pattern, int index)

1mysql> select regexp_extract('AbcdBCdefGHI','.*?([[:lower:]]+)',1);
2+--------------------------------------------------------+
3| regexp_extract('AbcdBCdefGHI', '.*?([[:lower:]]+)', 1) |
4+--------------------------------------------------------+
5| def                                                    |
6+--------------------------------------------------------+
7
8mysql> select regexp_extract('AbcdBCdefGHI','.*?([[:lower:]]+).*?',1);
9+-----------------------------------------------------------+
10| regexp_extract('AbcdBCdefGHI', '.*?([[:lower:]]+).*?', 1) |
11+-----------------------------------------------------------+
12| bcd                                                       |
13+-----------------------------------------------------------+

1regexp_extract, regexp

1regexp_replace(string initial, string pattern, string replacement)

1mysql> select regexp_replace('aaabbbaaa','b+','xyz');
2+------------------------------------------+
3| regexp_replace('aaabbbaaa', 'b+', 'xyz') |
4+------------------------------------------+
5| aaaxyzaaa                                |
6+------------------------------------------+
7
8mysql> select regexp_replace('aaabbbaaa','(b+)','<\\1>');
9+---------------------------------------------+
10| regexp_replace('aaabbbaaa', '(b+)', '<\1>') |
11+---------------------------------------------+
12| aaa<bbb>aaa                                 |
13+---------------------------------------------+
14
15mysql> select regexp_replace('123-456-789','[^[:digit:]]','');
16+---------------------------------------------------+
17| regexp_replace('123-456-789', '[^[:digit:]]', '') |
18+---------------------------------------------------+
19| 123456789                                         |
20+---------------------------------------------------+

1regexp_replace, regexp

1repeat(string str, int n)

1mysql> select repeat("abc", 3);
2+------------------+
3| repeat('abc', 3) |
4+------------------+
5| abcabcabc        |
6+------------------+

1repeat

1replace(string oriStr, string src, string dest)

1mysql> select replace('aaabbbaaa','b+','xyz');
2+-----------------------------------+
3| replace('aaabbbaaa', 'b+', 'xyz') |
4+-----------------------------------+
5| aaabbbaaa                         |
6+-----------------------------------+
7
8mysql> select replace('aaabbbaaa','bb','xyz');
9+-----------------------------------+
10| replace('aaabbbaaa', 'bb', 'xyz') |
11+-----------------------------------+
12| aaaxyzbaaa                        |
13+-----------------------------------+

1replace

1reverse(string a)

1mysql> select reverse('doris');
2+-----------------+
3| reverse('doris') |
4+-----------------+
5| olap            |
6+-----------------+

1reverse

1rpad(string str, int len, string pad)

1mysql> select rpad("hello", 10, 'xy');  
2+-------------------------+
3| rpad('hello', 10, 'xy') |
4+-------------------------+
5| helloxyxyx              |
6+-------------------------+

1rpad

1rtrim(string a)

1mysql> select rtrim('    today is friday   ');
2+---------------------------------+
3| rtrim('    today is friday   ') |
4+---------------------------------+
5|     today is friday             |
6+---------------------------------+
7
8mysql> select ltrim('    today is friday   ');
9+---------------------------------+
10| ltrim('    today is friday   ') |
11+---------------------------------+
12| today is friday                 |
13+---------------------------------+

1rtrim

1space(int n)

1mysql> select space(10);
2+------------+
3| space(10)  |
4+------------+
5|            |
6+------------+
7
8mysql> select space(20);
9+----------------------+
10| space(20)            |
11+----------------------+
12|                      |
13+----------------------+

1space

1split_part(string str, string splitStr, int num)

1select split_part('12,31,45,232', ',', 3);
2+------------------------------------+
3| split_part('12,31,45,232', ',', 3) |
4+------------------------------------+
5| 45                                 |
6+------------------------------------+

1split_part

1starts_with(string str, string strPrefix)

1mysql> select starts_with('baidu doris','doris');
2+-----------------------------------+
3| starts_with('baidu doris', 'doris') |
4+-----------------------------------+
5|                                 0 |
6+-----------------------------------+
7
8mysql> select starts_with('baidu doris','baidu');
9+------------------------------------+
10| starts_with('baidu doris', 'baidu') |
11+------------------------------------+
12|                                  1 |
13+------------------------------------+

1starts_with

1strleft(string a, int num_chars)
2
3left(string a, int num_chars)

1mysql> select strleft('doris@baidu',5);
2+--------------------------+
3| strleft('doris@baidu', 5) |
4+--------------------------+
5| doris@                    |
6+--------------------------+
7
8mysql> select left('doris@baidu',4);
9+-----------------------+
10| left('doris@baidu', 4) |
11+-----------------------+
12| doris                  |
13+-----------------------+

1strleft,left

1strright(string a, int num_chars)
2
3right(string a, int num_chars)

1mysql> select strright('doris@baidu',5);
2+---------------------------+
3| strright('doris@baidu', 5) |
4+---------------------------+
5| baidu                     |
6+---------------------------+
7
8mysql> select right('doris@baidu',6);
9+------------------------+
10| right('doris@baidu', 6) |
11+------------------------+
12| @baidu                 |
13+------------------------+

1strright,right

1substr(string a, int start [, int len])
2
3substring(string a, int start[, int len])

1mysql> select substring('baidudoris',6);
2+---------------------------+
3| substring('baidudoris', 6) |
4+---------------------------+
5| doris                      |
6+---------------------------+

1substr,substring

1trim(string a)

1mysql> select trim('    today is friday   ');
2+--------------------------------+
3| trim('    today is friday   ') |
4+--------------------------------+
5| today is friday                |
6+--------------------------------+

1trim

1upper(string a)
2
3ucase(string a)

1mysql> select upper('toDAY Is FridAy');
2+--------------------------+
3| upper('toDAY Is FridAy') |
4+--------------------------+
5| TODAY IS FRIDAY          |
6+--------------------------+
7
8mysql> select ucase('doris');
9+---------------+
10| ucase('doris') |
11+---------------+
12| DORIS          |
13+---------------+

1upper, ucase