字符串函数
所有文档

          数据仓库 Palo

          字符串函数

          Palo支持的字符串函数如下:

          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)
          • 功能:返回字符串第一个字符串对应的ascii 码
          • 返回类型:int类型

          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...)
          • 功能:将多个字符串连接起来
          • 返回类型: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/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)
          • 功能:字符串正则匹配。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('palo');
          +-----------------+
          | reverse('palo') |
          +-----------------+
          | 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 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)
          • 功能:返回字符串中最左边的num_chars个字符。
          • 返回类型:string类型

          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)
          • 功能:返回字符串中最右边的num_chars个字符。
          • 返回类型:string类型

          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])
          • 功能:求子串函数,返回第一个参数描述的字符串中从start开始长度为len的部分字符串。首字母的下标为1。
          • 返回类型:string类型

          Example

          mysql> select substring('baidupalo',6);
          +---------------------------+
          | substring('baidupalo', 6) |
          +---------------------------+
          | palo                      |
          +---------------------------+

          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('palo');
          +---------------+
          | ucase('palo') |
          +---------------+
          | PALO          |
          +---------------+

          Keywords

          upper, ucase
          上一篇
          类型转换函数
          下一篇
          数学函数