All Products
Search
Document Center

String functions

Last Updated: Sep 29, 2021

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 len is not specified, the returned substring starts from the pos position and ends at str.

  • If the value of pos is negative, the starting position is determined from str in inverted order.

  • If len is less than or equal to 0, or the starting position specified for POS is 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 strcount 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.

  • str1 and str2 must be strings, and POS and len must be integers. If any parameter is NULL, the result is always NULL.
  • The characters in str1 and str2 are considered as byte streams.
  • If pos is a negative value or exceeds the length of str1, str1 is returned.
  • When len is less than 0 or exceeds the length of str1, a combined string of str1 from the beginning to pos and str2 is 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.

  • str specifies the string to be searched. Multi-byte characters are supported.

  • pattern specifies a regular expression. The regular expression rules are compatible with the MySQL database.

  • position specifies 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.

  • occurrence specifies 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_param specifies 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 of str. NULL specifies to use the default value.

  • subexpr specifies 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 for pattern.

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)