OceanBase支持的字符串函数

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)


SUBSTRINGSUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)



和SUBSTR同语义。

SUBSTRSUBSTR(str,pos,len)

SUBSTR(str,pos)

SUBSTR(str FROM pos)

SUBSTR (str FROM pos FOR len)



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

• 
  str必须是字符串，pos和len必须是整数。任意参数为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)
• 
  

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



删除字符串所有前缀和（或）后缀。

• 
  remstr和str必须为字符串或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。若 expr 或 pat 任意一个为 NULL, 则结果为 NULL。 RLIKE 是REGEXP的同义词。
expr和 pat参数都必须为字符串或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,则返回一个空字符串。
若 str 或 count 为 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；若 str或 delim为空字符串，则返回空串；若 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,…)


返回参数 str在 str1,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)
•