全部产品
阿里云办公

字符串函数

更新时间:2017-06-07 13:26:11

OceanBase 1.0字符集为utf8mb4,暂不支持其它字符集。utf8mb4字符集对应的collation支持utf8mb4_bin、utf8mb4_general_ci这两种,默认为utf8mb4_general_ci。

CONCAT(str1,…,strN)

把一个或多个字符串连接成一个字符串。左右参数都必须是字符串类型或NULL,否则报错。如果执行成功,则返回连接后的字符串;参数中有一个值是NULL结果就是NULL。

说明:str参数可以是数值类型,系统能隐式转换为字符串处理。

Oceanbase>select concat('test'), concat('test','OceanBase'), concat('test', 'OceanBase', '1.0'), concat('test','OceanBase','1.0', NULL)\G;
*************************** 1. row ***************************
                        concat('test'): test
            concat('test','OceanBase'): testOceanBase
    concat('test', 'OceanBase', '1.0'): testOceanBase1.0
concat('test','OceanBase','1.0', NULL): NULL
1 row in set (0.00 sec)

SUBSTRING

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

和SUBSTR同语义。

SUBSTR

SUBSTR(str,pos,len)

SUBSTR(str,pos)

SUBSTR(str FROM pos)

SUBSTR (str FROM pos FOR len) 

返回一个子字符串,起始于位置pos,长度为len。使用FROM的格式为标准SQL语法。

  • str必须是字符串,poslen必须是整数。任意参数为NULL,结果总为NULL。

  • str中的中文字符被当做字节流看待。

  • 不带有len参数的时,则返回的子字符串从pos位置开始到原字符串结尾。

  • pos值为负数时,pos的位置从字符串的结尾的字符数起;为0时,可被看做1。

  • len小于等于0,或者pos指示的字符串位置不存在字符时,返回结果为空字符串。

Oceanbase>SELECT SUBSTR('abcdefg',3), SUBSTR('abcdefg',3,2), SUBSTR('abcdefg',-3), SUBSTR('abcdefg',3,-2), SUBSTR('abcdefg' from -4 for 2)\G;
*************************** 1. row ***************************
            SUBSTR('abcdefg',3): cdefg
          SUBSTR('abcdefg',3,2): cd
           SUBSTR('abcdefg',-3): efg
         SUBSTR('abcdefg',3,-2): 
SUBSTR('abcdefg' from -4 for 2): de
1 row in set (0.00 sec)

TRIM

TRIM([[{BOTH | LEADING | TRAILING}] [remstr] FROM] str)

删除字符串所有前缀和(或)后缀。

  • remstrstr必须为字符串或NULL类型。当参数中有NULL时结果总为NULL。

  • 若未指定BOTH、LEADIN或TRAILING,则默认为BOTH。

  • remstr为可选项,在未指定情况下,删除空格。

Oceanbase>SELECT TRIM(' bar '),
    -> TRIM(LEADING 'x' FROM 'xxxbarxxx'),
    -> TRIM(BOTH 'x' FROM 'xxxbarxxx'),
    -> TRIM(TRAILING 'x' FROM 'xxxbarxxx')\G;
*************************** 1. row ***************************
                      TRIM(' bar '): bar
 TRIM(LEADING 'x' FROM 'xxxbarxxx'): barxxx
    TRIM(BOTH 'x' FROM 'xxxbarxxx'): bar
TRIM(TRAILING 'x' FROM 'xxxbarxxx'): xxxbar
1 row in set (0.01 sec)

LENGTH(str)

返回字符串的长度,单位为字节。参数必须是字符串类型或NULL,否则报错。 如果执行成功,结果是INT型整数,表示字符串长度;当参数是NULL结果为NULL。

str参数为数值类型时,系统能隐式转换为字符串类型。

Oceanbase>SELECT LENGTH('text');
+----------------+
| LENGTH('text') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

Oceanbase>select length(-1.23);
+---------------+
| length(-1.23) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

Oceanbase>select length(1233e);
ERROR 1054 (42S22): Unknown column '1233e' in 'field list'

UPPER(str)

将字符串转化为大写字母的字符。参数必须是字符串类型。若为NULL,结果总为NULL。

str参数为数值类型时,能隐式转换为字符串类型。

由于中文编码的字节区间与ASCII大小写字符不重合,对于中文,UPPER可以很好的兼容。

Oceanbase>SELECT UPPER('OceanBase您好!');
+-----------------------------+
| UPPER('OceanBase您好!')    |
+-----------------------------+
| OCEANBASE您好!             |
+-----------------------------+
1 row in set (0.00 sec)

Oceanbase>select upper(e);
ERROR 1054 (42S22): Unknown column 'e' in 'field list'

Oceanbase>select upper(1.235.);
ERROR 1064 (42000): You have an error in your SQL syntax; 

LOWER(str)

将字符串转化为小写字母的字符。参数必须是字符串类型。若为NULL,结果总为NULL。

str参数为数值类型时,能隐式转换为字符串类型。

由于中文编码的字节区间与ASCII大小写字符不重合,对于中文,LOWER可以很好的兼容。

Oceanbase>SELECT LOWER('OceanBase您好!');
+-----------------------------+
| LOWER('OceanBase您好!')    |
+-----------------------------+
| oceanbase您好!             |
+-----------------------------+
1 row in set (0.00 sec)

Oceanbase>select lower(1.23) ;
+-------------+
| lower(1.23) |
+-------------+
| 1.23        |
+-------------+
1 row in set (0.00 sec)

Oceanbase>select lower(1.23h);
ERROR 1583 (42000): Incorrect parameters in the call to native function 'lower'

Oceanbase>select lower(1.23e);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'lower'

HEX(str)

将字符串转化为十六进制数显示。输入为NULL时,输出也为NULL。

str是数值时,输出整数的十六进制表示;

str输入是字符串的时候,返回值为str的十六进制字符串表示,其中每个str里的每个字符被转化为两个十六进制数字。

Oceanbase>SELECT HEX(255);
        -> FF
Oceanbase>SELECT HEX('abc');
        -> 616263

Oceanbase>SELECT HEX('OceanBase'),
    ->     HEX(123),
    ->     HEX(0x0123);
+--------------------+----------+-------------+
| HEX('OceanBase')   | HEX(123) | HEX(0x0123) |
+--------------------+----------+-------------+
| 4F6365616E42617365 | 7B       | 0123        |
+--------------------+----------+-------------+
1 row in set (0.01 sec)    

Oceanbase>select hex(0x012);
+------------+
| hex(0x012) |
+------------+
| 0012       |
+------------+
1 row in set (0.00 sec)

UNHEX(str)

HEX(str)的反向操作,即将参数中的每一对十六进制数字理解为一个数字,并将其转化为该数字代表的字符。结果字符以二进制字符串的形式返回。

str必须是字符串类型或NULL。当str是合法的十六进制值时将按照十六进制到字节流的转换算法进行,当str不是十六进制字符串的时候返回NULL。当str为NULL的时候输出是NULL。

Oceanbase>SELECT HEX('OceanBase'),
->     UNHEX('4f6365616e42617365'),
->     UNHEX(HEX('OceanBase')),
->     UNHEX(NULL)\G;
*************************** 1. row ***************************
           HEX('OceanBase'): 4F6365616E42617365
UNHEX('4f6365616e42617365'): OceanBase
    UNHEX(HEX('OceanBase')): OceanBase
                UNHEX(NULL): NULL
1 row in set (0.00 sec)

Oceanbase>select unhex(abc);
ERROR 1054 (42S22): Unknown column 'abc' in 'field list';

INT2IP(int_value)

注:INT2IP为OceanBase特有函数。

将一个整数转换成IP地址。

  • 输入数据类型必须为INT。若输入为NULL,则输出为NULL。若输入的数字大于MAX_INT32或小于0则输出为NULL。
Oceanbase>SELECT INT2IP(16777216),
    -> HEX(16777216),
    -> INT2IP(1);
+------------------+---------------+-----------+
| INT2IP(16777216) | HEX(16777216) | INT2IP(1) |
+------------------+---------------+-----------+
| 1.0.0.0          | 1000000       | 0.0.0.1   |
+------------------+---------------+-----------+
1 row in set (0.00 sec)

IP2INT('ip_addr')

注:IP2INT为OceanBase特有函数。

将字符串表示的IP地址转换成整数内码表示。

  • 输入数据类型必须为字符串类型。若输入为NULL,则输出为NULL。若输入的IP地址不是一个正确的IP地址(包含非数字字符,每一个ip segment的数值大小超过256等),则输出为NULL。

  • 仅支持ipv4地址,暂不支持ipv6地址。

Oceanbase>SELECT IP2INT('0.0.0.1'),
    -> HEX(IP2INT('0.0.0.1')),
    -> HEX(IP2INT('1.0.0.0')),
    -> IP2INT('1.0.0.257')\G;
*************************** 1. row ***************************
     IP2INT('0.0.0.1'): 1
HEX(IP2INT('0.0.0.1')): 1
HEX(IP2INT('1.0.0.0')): 1000000
   IP2INT('1.0.0.257'): NULL
1 row in set (0.01 sec)

[NOT] LIKE str2 [ESCAPE str3]

字符串通配符匹配。左右参数都必须是字符串类型或NULL,否则报错。如果执行成功,结果是TRUE或者FALSE,或某一个参数是NULL结果就是NULL。

通配符包括“%”和“_”:

  • “%”表示匹配任何长度的任何字符,且匹配的字符可以不存在。

  • “_”表示只匹配单个字符,且匹配的字符必须存在。

如果你需要查找“a_c”,而不是“abc”时,可以使用OceanBase的转义字符“\\”,即可以表示为“a\\_c”。

ESCAPE用于定义转义符,即表示如果str2中包含str3,那么在匹配时,str3后的字符为普通字符处理,例如:LIKE 'abc%' ESCAPE 'c',此时“c”为转义符,而“%”为普通字符,不再作为转义字符,本语句匹配的字符串为“ab%”。

Oceanbase>SELECT 'ab%' LIKE 'abc%' ESCAPE 'c';
+------------------------------+
| 'ab%' LIKE 'abc%' ESCAPE 'c' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

用ESCAPE定义转义符时,长度为1的字符串; 且 % 和 _ 不能用来作为转义字符。

Oceanbase>select 'a_c' like 'a\\_c';
+--------------------+
| 'a_c' like 'a\\_c' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

Oceanbase>select 'abc_' like 'abcdd_' escape 'dd';
ERROR 1064 (42000): Incorrect arguments to ESCAPE

expr [NOT] REGEXP | RLIKE pat

执行字符串表达式expr和模式pat的模式匹配。若expr匹配 pat,则返回 1; 否则返回0。若 exprpat 任意一个为 NULL, 则结果为 NULL。 RLIKE 是REGEXP的同义词。

exprpat参数都必须为字符串或NULL,支持隐式转换成字符串类型。数据类型不匹配的,报错。PATTERN必须为合法的正则表达式,否则报错。

Oceanbase>select 1234 regexp 1;
+---------------+
| 1234 regexp 1 |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

Oceanbase>select 'hello'  rlike 'h%';
+---------------------+
| 'hello'  rlike 'h%' |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec) 

Oceanbase>select 1234 regexp ^y;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near '^y' at line 1 

Oceanbase>select yunzhi regexp '^y';
ERROR 1054 (42S22): Unknown column 'yunzhi' in 'field list'

Oceanbase>select 'hello' not  rlike '*h*';
ERROR 1139 (42000): Got error 'empty (sub)expression' from regexp

REPEAT(str, count)

返回一个由重复count次数的字符串str组成的字符串 。 若 count <= 0,则返回一个空字符串。

strcount 为 NULL,则返回 NULL。

str为数值类型时,系统隐式转换为字符串类型。

count支持隐式转换成数值类型,如果转换失败,则相当于count为0。

Oceanbase>select repeat('1',-1),  repeat(null,null),repeat('test',4);
+----------------+-------------------+------------------+
| repeat('1',-1) | repeat(null,null) | repeat('test',4) |
+----------------+-------------------+------------------+
|                  | NULL               | testtesttesttest |
+----------------+-------------------+------------------+
1 row in set (0.00 sec)

Oceanbase>select repeat(11111,'2');
+-------------------+
| repeat(11111,'2') |
+-------------------+
| 1111111111        |
+-------------------+
1 row in set (0.00 sec)

SUBSTRING_INDEX(str, delim, count)

在定界符 delim 以及count 出现前,从字符串str返回字符串。若count为正值,则返回最终定界符(从左边开始)左边的一切内容。若count为负值,则返回定界符(从右边开始)右边的一切内容。任意一个参数为NULL,返回NULL;若strdelim为空字符串,则返回空串;若count=0,返回空串。

str, delim, count参数支持数值与字符串的隐式转换。

Oceanbase>select substring_index('abcdabc', 'abc', 0), substring_index('abcdabc', 'abc', 1), substring_index('abcdabc', 'abc', 2), substring_index('abcdabc', 'abc', 3), substring_index('abcdabc', 'abc', -1), substring_index('abcdabc', 'abc', -2), substring_index('abcdabc', 'abc', -3)\G;
*************************** 1. row ***************************
 substring_index('abcdabc', 'abc', 0):
 substring_index('abcdabc', 'abc', 1):
 substring_index('abcdabc', 'abc', 2): abcd
 substring_index('abcdabc', 'abc', 3): abcdabc
substring_index('abcdabc', 'abc', -1):
substring_index('abcdabc', 'abc', -2): dabc
substring_index('abcdabc', 'abc', -3): abcdabc
1 row in set (0.00 sec)

LOCATE(substr,str) , LOCATE(substr,str,pos)

第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中,则返回值为0。

Oceanbase>SELECT LOCATE('bar', 'foobarbar');
        -> 4
Oceanbase>SELECT LOCATE('xbar', 'foobar');
        -> 0
Oceanbase>SELECT LOCATE('bar', 'foobarbar',5);
        -> 7

INSTR(str,substr)

返回字符串str中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同,除非参数的顺序被颠倒。

Oceanbase>SELECT INSTR('foobarbar', 'bar');
        -> 4
Oceanbase>SELECT INSTR('xbar', 'foobar');
        -> 0

REPLACE(str, from_str, to_str)

返回字符串str以及所有被字符to_str替代的字符串from_str

Oceanbase>SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www');
+---------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www') |
+---------------------------------------------------------+
| wwwefg.gpg.nowdew.wwwdwwwe                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FIELD(str,str1,str2,str3,…)

返回参数strstr1,str2,str3,…列表中的索引位置(从1开始的位置)。在找不到str的情况下,返回值为0。

如果所有的对于FIELD()的参数均为字符串,则所有参数均按照字符串进行比较。如果所有的参数均为数字,则按照数字进行比较。否则,参数均按照double类型进行比较。

如果str为NULL,则返回值为0,原因是NULL不能同任何值进行同等比较。FILED()是ELT()的补数。

Oceanbase>select field('abc','abc1','abc2','abc','abc4','abc'), field(NULL, 'null1', NULL);
+-----------------------------------------------+----------------------------+
| field('abc','abc1','abc2','abc','abc4','abc') | field(NULL, 'null1', NULL) |
+-----------------------------------------------+----------------------------+
|                   3 |                          0 |
+-----------------------------------------------+----------------------------+
1 row in set (0.00 sec)

ELT(N, str1, str2, str3,…)

若N=1,则返回值为str1, 若N=2,则返回值为str2,以此类推。若N小于1或大于参数的数目,则返回值为NULL。ELT()是FIELD()的补数。

Oceanbase>select elt(3, 'abc1', 'abc2', 'abc', 'abc4', 'abc'), elt(0, 'null1', NULL);
+----------------------------------------------+-----------------------+
| elt(3, 'abc1', 'abc2', 'abc', 'abc4', 'abc') | elt(0, 'null1', NULL) |
+----------------------------------------------+-----------------------+
| abc                                          | NULL                  |
+----------------------------------------------+-----------------------+
1 row in set (0.00 sec)

INSERT (str1,pos,len,str2)

返回字符串str1, 字符串中起始于pos位置,长度为len的子字符串将被str2取代。如果 pos 超过字符串长度,则返回值为原始字符串。 假如 len 的长度大于其它字符串的长度,则从位置pos开始替换。若任何一个参数为null,则返回值为NULL。这个函数支持多字节字元。

  • str1和str2必须是字符串,pos和len必须是整数。任意参数为 NULL,结果总为 NULL。
  • str1和str2中的文字符被当做字节流看待。
  • pos 值为负数或者大于 str1 长度时,返回 str1;
  • 当len小于0,或大于 str1 的长度时,返回结果为str1从开头到pos位置的串,和str2的组合串。
Oceanbase>select insert('Quadratic',-2,100,'What'), insert('Quadratic',7,3,'What'),
    -> insert('Quadratic',-1,3,'What'), insert('Quadratic',10,3,'What'), insert('Quadratic',5,-1,''),
    -> insert('Quadratic',7,-1,'What')\G;
*************************** 1. row ***************************
insert('Quadratic',-2,100,'What'): Quadratic
   insert('Quadratic',7,3,'What'): QuadraWhat
  insert('Quadratic',-1,3,'What'): Quadratic
  insert('Quadratic',10,3,'What'): Quadratic
      insert('Quadratic',5,-1,''): Quad
  insert('Quadratic',7,-1,'What'): QuadraWhat
1 row in set (0.01 sec)