CONCAT
Declaration
CONCAT(str1, .., strN)
Description
This function concatenates multiple strings into one string. If the parameters include a NULL value, the function returns NULL.
Example
obclient> SELECT
CONCAT('test','obclient', '1.0'),
CONCAT('test','obclient', NULL)
\G
*************************** 1. row ***************************
CONCAT('test','obclient', '1.0'): testobclient1.0
CONCAT('test','obclient', NULL): NULL
1 row in set (0.01 sec)
CONCAT_WS
Declaration
CONCAT_WS(separator, str1, .., strN)
Description
This function concatenates multiple strings into one string, with adjacent strings separated with the separator specified for separator. If the parameters include a NULL value, the NULL value is ignored.
Example
obclient> SELECT
CONCAT_WS('_', 'First', 'Second'),
CONCAT_WS('_', 'First', NULL, 'Second')
\G
*************************** 1. row ***************************
CONCAT_WS('_', 'First', 'Second'): First_Second
CONCAT_WS('_', 'First', NULL, 'Second'): First_Second
1 row in set (0.00 sec)
FORMAT
Declaration
FORMAT(X,D)
Description
This function formats a numeric value X into the format of "#,###,###.##".The formatted value is rounded off to D decimal places and is returned as a string. If the integer part exceeds three digits, a comma is used as the separator at the thousands place. If D is 0, the result has no decimal point or fractional part.
Example
obclient> SELECT FORMAT(12332.123456, 4) from DUAL;
+-------------------------+
| format(12332.123456, 4) |
+-------------------------+
| 12,332.1235 |
+-------------------------+
1 row in set (0.00 sec)
obclient> SELECT FORMAT(12332.1, 4) from DUAL;
+--------------------+
| format(12332.1, 4) |
+--------------------+
| 12,332.1000 |
+--------------------+
1 row in set (0.01 sec)
obclient> SELECT FORMAT(12332.2, 0) from DUAL;
+--------------------+
| format(12332.2, 0) |
+--------------------+
| 12,332 |
+--------------------+
1 row in set (0.00 sec)
SUBSTR
Declaration
SUBSTR(str, pos)
SUBSTR(str, pos, len)
SUBSTR(str FROM pos)
SUBSTR(str FROM pos FOR len)
Description
This function returns the substring of str. The starting position is pos, and the length is len. If the parameters include a NULL value, the function returns NULL.
- If
lenis not specified, the returned substring starts from theposposition and ends atstr.
- If the value of
posis negative, the starting position is determined fromstrin inverted order.
- If
lenis less than or equal to 0, or the starting position specified forPOSis invalid, an empty string is returned.
Example
obclient> 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.01 sec)
SUBSTRING
Declaration
SUBSTRING(str, pos)
SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos)
SUBSTRING(str FROM pos FOR len)
Description
The same as SUBSTR.
TRIM
Declaration
TRIM([[{BOTH | LEADING | TRAILING}] [remstr] FROM] str)
Description
This function deletes all prefixes and/or suffixes of a string. The default value is BOTH. If the parameters include a NULL value, the function returns NULL.
Example
obclient> 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)
LTRIM
Declaration
LTRIM(str)
Description
This function removes the leading spaces from a string.
RTRIM
Declaration
RTRIM(str)
Description
This function removes the trailing spaces from a string.
ASCII
Declaration
ASCII(str)
Description
This function returns the ASCII value of the leftmost character in a string.
ORD
Declaration
ORD(str)
Description
This function returns the code of the leftmost character in a string. If the leftmost character of a string is a multi-byte character, ORD(str) returns the code of the multi-byte character based on the following formula:
(1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256^2)...
Example
obclient> SELECT ORD ('China');
+---------------+
| ORD ('China') |
+---------------+
| 67 |
+---------------+
1 row in set (0.01 sec)
LENGTH
Declaration
LENGTH(str)
Description
This function returns the length of str in bytes.
Example
obclient> SELECT LENGTH('hello');
+-----------------+
| LENGTH('hello') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.01 sec)
CHAR_LENGTH
Declaration
CHAR_LENGTH(str)
Description
This function returns the number of characters in a string.
Example
obclient> SELECT CHAR_LENGTH('hello');
+----------------------+
| CHAR_LENGTH('hello') |
+----------------------+
| 5 |
+----------------------+
1 row in set (0.00 sec)
UPPER
Declaration
UPPER(str)
Description
This function converts lowercase letters in a string into uppercase letters.
Example
obclient> SELECT UPPER('obclientHello!');;
+----------------------------+
| UPPER('obclientHello!') |
+----------------------------+
| OBCLIENTHELLO! |
+----------------------------+
1 row in set (0.00 sec)
LOWER
Declaration
LOWER(str)
Description
This function converts uppercase letters in a string into lowercase letters.
Example
obclient> SELECT LOWER('obclientHello!');
+-----------------------------+
| LOWER('obclientHello!') |
+-----------------------------+
| obclienthello! |
+-----------------------------+
1 row in set (0.01 sec)
HEX
Declaration
HEX(str)
Description
This function converts a number or string into a hexadecimal string.
Example
obclient> SELECT HEX(255), HEX('abc');
+----------+------------+
| HEX(255) | HEX('abc') |
+----------+------------+
| FF | 616263 |
+----------+------------+
1 row in set (0.00 sec)
UNHEX
Declaration
UNHEX(str)
Description
This function converts a hexadecimal string to a normal string.
Example
obclient> SELECT UNHEX('4f6365616e42617365');
+-----------------------------+
| UNHEX('4f6365616e42617365') |
+-----------------------------+
| OceanBase |
+-----------------------------+
1 row in set (0.01 sec)
MD5
Declaration
MD5(str)
Description
This function returns the MD5 value of the specified string.
Example
obclient> SELECT MD5(1);
+----------------------------------+
| MD5(1) |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------+
1 row in set (0.00 sec)
INT2IP
Declaration
INT2IP(int_value)
Description
This function converts an integer internal code into an IP address.
Example
obclient> SELECT
INT2IP(16777216),
HEX(16777216),
INT2IP(1)
\G
*************************** 1. row ***************************
INT2IP(16777216): 1.0.0.0
HEX(16777216): 1,000,000
INT2IP(1): 0.0.0.1
1 row in set (0.01 sec)
IP2INT
Declaration
IP2INT('ip_addr')
Description
This function converts an IP address into an integer internal code.
Example
obclient> SELECT
IP2INT('0.0.0.1'),
HEX(IP2INT('0.0.0.1')),
HEX(IP2INT('1.0.0.0'))
\G
*************************** 1. row ***************************
IP2INT('0.0.0.1'): 1
HEX(IP2INT('0.0.0.1')): 1
HEX(IP2INT('1.0.0.0')): 1,000,000
1 row in set (0.01 sec)
LIKE
Declaration
str1 [NOT] LIKE str2 [ESCAPE str3]
Description
This function matches a string with wildcard characters. If the parameters include a NULL value, the function returns NULL.
Wildcard characters include:
%: matches a string of any length.
_: matches a single character.
ESCAPE is used to define the escape characters. If str2 contains str3, the characters after str3 are taken as ordinary characters during matching.
Example
obclient> SELECT 'ab%' LIKE 'abc%' ESCAPE 'c';
+------------------------------+
| 'ab%' LIKE 'abc%' ESCAPE 'c' |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.01 sec)
REGEXP
Declaration
str [NOT] REGEXP | RLIKE pat
Description
This function is used for regex matching. If the parameters include a NULL value, the function returns NULL.
Example
obclient> SELECT
1234 REGEXP 1
'hello' RLIKE 'h%'
\G
*************************** 1. row ***************************
1234 REGEXP 1: 1
'hello' RLIKE 'h%': 0
1 row in set (0.01 sec)
REPEAT
Declaration
REPEAT(str, count)
Description
This function returns the string of repeating str``count times. When count is less than or equal to 0, an empty string is returned. If the parameters include a NULL value, the function returns NULL.
Example
obclient> SELECT
REPEAT('1', -1)
REPEAT(null, null)
REPEAT('Abc', 4)
\G
*************************** 1. row ***************************
REPEAT('1', -1):
REPEAT(null, null): NULL
REPEAT('Abc', 4): AbcAbcAbcAbc
1 row in set (0.01 sec)
SPACE
Declaration
SPACE(N)
Description
This function returns a string with N spaces.
SUBSTRING_INDEX
Declaration
SUBSTRING_INDEX(str, delim, count)
Description
This function returns a string from str before delim and count appear. If count is a positive value, the content on the left of the final delimiter (starting from the left) is returned. If count is a negative value, the content on the right of the delimiter (starting from the right) is returned. If any parameter is NULL, NULL is returned. If str or delim is an empty string, an empty string is returned. If count is 0, an empty string is returned.
str, delim, and count support implicit conversion between numeric values and strings.
Example
obclient>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
Declaration
LOCATE(substr,str) , LOCATE(substr,str,pos)
Description
The first syntax returns the position of the first occurrence of substr of str. The first syntax returns the position of the first occurrence of substr of str, and the starting position is pos. If substr is not in str, 0 is returned.
Example
obclient> SELECT LOCATE('bar', 'foobarbar'), LOCATE('xbar', 'foobar'), LOCATE('bar', 'foobarbar',5);
+----------------------------+--------------------------+------------------------------+
| LOCATE('bar', 'foobarbar') | LOCATE('xbar', 'foobar') | LOCATE('bar', 'foobarbar',5) |
+----------------------------+--------------------------+------------------------------+
| 4 | 0 | 7 |
+----------------------------+--------------------------+------------------------------+
1 row in set (0.00 sec)
POSITION
Declaration
POSITION(substr IN str)
Description
The same as LOCATE.
INSTR
Declaration
INSTR(str,substr)
Description
This function returns the position of the first occurrence of a substring in str. It is the same as the LOCATE() function with two parameters, unless the parameter sequence is reversed.
Example
obclient> SELECT INSTR('foobarbar', 'bar'), INSTR('xbar', 'foobar');
+---------------------------+-------------------------+
| INSTR('foobarbar', 'bar') | INSTR('xbar', 'foobar') |
+---------------------------+-------------------------+
| 4 | 0 |
+---------------------------+-------------------------+
1 row in set (0.00 sec)
REPLACE
Declaration
REPLACE(str, from_str, to_str)
Description
This function returns the str string with all occurrences of the from_str string replaced by the to_str string.
Example
obclient> 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
Declaration
FIELD(str,str1,str2,str3,...)
Description
This function returns the index position (starting from str1) of str in the list of str1, str2, str3, ... If str is not found, 0 is returned.
If all FIELD() parameters are strings, all parameters are compared as strings. If all parameters are numbers, the parameters are compared as numbers. Otherwise, the parameters are compared as double.
If str is NULL, the return value is 0. This is because NULL is neither equal to nor unequal to any value. FILED() is the complement of ELT().
Example
obclient> 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
Declaration
ELT(N, str1, str2, str3,...)
Description
If N = 1, the return value is str1. If N = 2, the return value is str2, and so forth. If N is less than 1 or greater than the number of parameters, NULL is returned. ELT() is the complement of FIELD().
Example
obclient> 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
Declaration
INSERT (str1,pos,len,str2)
Description
This function returns the str1 string, which starts from pos. Substrings with a length of len are replaced with str2. If pos exceeds the string length, the original string is returned. If len is greater than the length of other strings, the substring starting from the position pos is replaced. If any parameter is NULL, NULL is returned. This function supports multi-byte characters.
str1andstr2must be strings, andPOSandlenmust be integers. If any parameter is NULL, the result is always NULL.
- The characters in
str1andstr2are considered as byte streams.
- If
posis a negative value or exceeds the length ofstr1,str1is returned.
- When
lenis less than 0 or exceeds the length ofstr1, a combined string ofstr1from the beginning toposandstr2is returned.
Example
obclient> 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)
LPAD
Declaration
LPAD(str, len, padstr)
Description
This function fills the string specified for padstr into the left side of str to the length specified for len. If the length of str exceeds len, str is truncated.
Example
obclient> SELECT LPAD('hi',4,'??');
+-------------------+
| LPAD('hi',4,'??') |
+-------------------+
| ??hi |
+-------------------+
1 row in set (0.01 sec)
obclient> SELECT LPAD('hi',1,'??');
+-------------------+
| LPAD('hi',1,'??') |
+-------------------+
| h |
+-------------------+
1 row in set (0.00 sec)
RPAD
Declaration
RPAD(str, len, padstr)
Description
This function fills the string specified for padstr into the right side of str to the length specified for len. If the length of str exceeds len, str is truncated.
Example
obclient> SELECT RPAD('hi',4,'??');
+-------------------+
| RPAD('hi',4,'??') |
+-------------------+
| hi?? |
+-------------------+
1 row in set (0.00 sec)
obclient> SELECT RPAD('hi',1,'??');
+-------------------+
| RPAD('hi',1,'??') |
+-------------------+
| h |
+-------------------+
1 row in set (0.00 sec)
UUID
Declaration
uuid()
Description
This function generates a globally unique ID.
Example
obclient> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| f756a1f6-4de6-11e8-90af-90b11c53e421 |
+--------------------------------------+
1 row in set (0.00 sec)
BIN
Declaration
bin(N)
Description
This function returns the binary form of the number N.
Example
obclient> SELECT BIN(12);
+---------+
| BIN(12) |
+---------+
| 1100 |
+---------+
1 row in set (0.00 sec)
QUOTE
Declaration
quote(str)
Description
This function quotes a string to generate a result that can be used as a properly escaped value in an SQL statement. The returned string is enclosed by single quotation marks (''). Each instance of backslash (\), single quote ('), ASCII NUL, and Control-Z is preceded by a backslash. If a parameter is NULL, the return value is 'NULL' without single quotation marks.
Example
obclient> SELECT QUOTE('Don\'t!');
+------------------+
| QUOTE('Don\'t!') |
+------------------+
| 'Don\'t!' |
+------------------+
1 row in set (0.00 sec)
obclient> SELECT QUOTE(NULL);
+-------------+
| QUOTE(NULL) |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
REGEXP_SUBSTR
Declaration
REGEXP_SUBSTR(str,pattern,[position[,occurrence[,match_param[,subexpr]]]])
Description
This function searches in the str string for the substring that matches the regular expression specified for pattern, and returns NULL if the substring does not exist. This function supports multi-byte characters. NULL is returned if any other parameter except match_para is NULL.
strspecifies the string to be searched. Multi-byte characters are supported.
patternspecifies a regular expression. The regular expression rules are compatible with the MySQL database.
positionspecifies the starting position for the search. The value must be a positive integer, and an error is returned if the value is less than or equal to 0. NULL is returned if NULL is input. The default value is 1, which specifies to search from the first character.
occurrencespecifies the ordinal number of occurrence at which the matched value is to be returned. The value must be a positive integer, and an error is returned if the value is less than or equal to 0. NULL is returned if NULL is input. The default value is 1, which specifies to return the first matched result.
match_paramspecifies the search parameter. The value is a string. Only two characters 'i' and 'c' are supported. i indicates case-insensitive and c indicates case-sensitive. An error message is returned for other characters. The default value depends on the collection of the character set ofstr. NULL specifies to use the default value.
subexprspecifies the ordinal number of the group in the regular expression whose value is to be returned. The value is an integer greater than or equal to 0. An error is returned if the value is less than 0. The default value is 0, which means to return the substring that matches the regular expression specified forpattern.
Example
obclient> select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) from dual;
+----------------------------------------------------------------------------------+
| regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) |
+----------------------------------------------------------------------------------+
| have |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
obclient> select regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) from dual;
+----------------------------------------------------------+
| regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) |
+----------------------------------------------------------+
| the |
+----------------------------------------------------------+
1 row in set (0.01 sec)