String Functions

ASCII

ASCII(str)

Description: Returns the ASCII code of the leftmost character of str. If str is empty, 0 is returned. If str is NULL, NULL is returned.

Return type: INT

Examples:

m_db=# SELECT ASCII('');
 ascii 
-------
     0
(1 row)

m_db=# SELECT ASCII('abc');
 ascii 
-------
    97
(1 row)

m_db=# SELECT ASCII(NULL);
 ascii 
-------

(1 row)

BIT_LENGTH

BIT_LENGTH(str)

Description: Returns the bit length of str.

Return type: BIGINT

Examples:

m_db=# SELECT BIT_LENGTH('text');
 bit_length 
------------
         32
(1 row)

CHAR_LENGTH

CHAR_LENGTH(str)

Description: Returns the number of characters contained in str. A multi-byte character is counted as one character. For example, if you enter two 2-byte characters, LENGTH() returns 4, and CHAR_LENGTH() returns 2.

Return type: BIGINT

Examples:

m_db=# SELECT CHAR_LENGTH('text');
 char_length 
-------------
           4
(1 row)

CHARACTER_LENGTH

CHARACTER_LENGTH(str)

Description: The function and usage are the same as those of CHAR_LENGTH.

Return type: BIGINT

Examples:

m_db=# SELECT CHARACTER_LENGTH('text');
 character_length 
------------------
                4
(1 row)

CONCAT

CONCAT(str1, str2, ..., strN)

Description: Concatenates N character strings based on the input parameter sequence and returns the concatenated character string. If any string is NULL, NULL is returned.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT CONCAT('hello', 'world', '!');
   concat    
-------------
 helloworld!
(1 row)

m_db=# SELECT CONCAT('hello', NULL, '!');
 concat 
--------

(1 row)

CONCAT_WS

CONCAT_WS(separator, str1, str2, ..., strN)

Description: Concatenates multiple strings in sequence and separates adjacent strings using separator. If separator is NULL, NULL is returned. If the string to be concatenated is NULL, it is ignored.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT CONCAT_WS(',', 'hello', NULL, 'world', '!');
   concat_ws   
---------------
 hello,world,!
(1 row)

m_db=# SELECT CONCAT_WS(NULL, 'hello', NULL, 'world', '!');
 concat_ws 
-----------

(1 row)

FIELD

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

Description: The field function returns the position of str in the {str, str1, str2, str3, ...} list. The position increases from 1. If 0 is returned, str is not in the {str, str1, str2, str3, ...} list.

Examples:

m_db=# SELECT FIELD(1,2,1);
 field 
------
     2
(1 row)

m_db=# SELECT FIELD('a','b','a');
 field 
------
     2
(1 row)

m_db=# SELECT FIELD('a',1,'b','a');
WARNING:  Truncated incorrect double value: 'a'
CONTEXT:  referenced column: field
WARNING:  Truncated incorrect double value: 'b'
CONTEXT:  referenced column: field
 field
-------
     2
(1 row)

FIND_IN_SET

FIND_IN_SET(str, strlist)

Description: Returns the position of str in strlist, starting from 1 in ascending order. The string list strlist is a string that contains multiple sub-strings. The sub-strings are separated by commas (,).

Examples:

m_db=# SELECT FIND_IN_SET('e', 'a,b,c;d,e');
 find_in_set 
-------------
           4
(1 row)

m_db=# SELECT FIND_IN_SET('a,', 'a,b,c;d,e');
 find_in_set 
-------------
           0
(1 row)

m_db=# SELECT FIND_IN_SET(NULL, 'a,b,c;d,e');
 find_in_set 
-------------

(1 row)

HEX

• HEX(str)

  Description: Converts str into a hexadecimal string. Each byte of each character in str is converted into two hexadecimal digits. The reverse operation is UNHEX.

  Return value type: TEXT

  Examples:

  m_db=# SELECT HEX('abc'), UNHEX(HEX('abc'));
    hex   | unhex 
  --------+-------
   616263 | abc
  (1 row)

• HEX(N)

  Description: Converts the number N into a hexadecimal string.

  Return value type: TEXT

  Examples:

  m_db=# SELECT HEX(255);
   hex 
  -----
   FF
  (1 row)

LCASE

LCASE(str)

Description: Converts uppercase characters in a string to lowercase letters. The functions and usage of LCASE are the same as those of LOWER.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT LCASE('Hello,world!');
    lcase     
--------------
 hello,world!
(1 row)

LEFT

LEFT(str, len)

Description: Returns the leftmost len characters of str. If str or len is NULL, NULL is returned. The number of characters is specified by len.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT LEFT('abcdef', 3);
 left 
------
 abc
(1 row)

m_db=# SELECT LEFT('abcdef', NULL);
 left 
------

(1 row)

m_db=# SELECT LEFT('abcdef', 11);
  left  
--------
 abcdef
(1 row)

LENGTH

LENGTH(str)

Description: Returns the number of bytes contained in str. A multi-byte character is counted as multiple bytes. For example, if you enter two 2-byte characters, LENGTH() returns 4, and CHAR_LENGTH() returns 2.

Return type: BIGINT

Examples:

m_db=# SELECT LENGTH('text');
 char_length 
-------------
           4
(1 row)

LOWER

LOWER(str)

Description: Converts uppercase letters in a string to lowercase letters. The function and usage of LOWER are the same as those of LCASE.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT LOWER('Hello,world!');
    lower     
--------------
 hello,world!
(1 row)

LPAD

LPAD(str, len, padstr)

Description: Uses padstr to pad str from the left until the length is the value of len. The length of the return value is affected by the GUC parameter max_allowed_packet. The range of the maximum value is as follows: Min(max_allowed_packet/Maximum length of a single character in the character set returned by the function, 1073741819).

Parameters: Table 1 describes the parameters.

Return value type: TEXT or LONGBLOB

• If the length of str is greater than len, len characters on the left of str are returned.
• If str, len, or padstr is NULL, NULL is returned.
• If the value of len is negative, NULL is returned.

Examples:

m_db=# SELECT LPAD('hello', 10, 'abc');
    lpad    
------------
 abcabhello
(1 row)

m_db=# SELECT LPAD('hello', 3, 'abc');
 lpad 
------
 hel
(1 row)

m_db=# SELECT LPAD('hello', 10, NULL);
 lpad 
------

(1 row)

m_db=# SELECT LPAD('hello', -10, 'abc');
 lpad 
------

(1 row)

LTRIM

LTRIM(str)

Description: Deletes spaces on the left of str.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT LTRIM('  hello   ');
  ltrim   
----------
 hello   
(1 row)

REPEAT

REPEAT(str, count)

Description: Returns a string consisting of str repeated count times. If count is less than or equal to 0, NULL is returned. If str or count is NULL, NULL is returned. The length of the return value is affected by the GUC parameter max_allowed_packet. The range of the maximum value is as follows: Min(max_allowed_packet, 1073741819).

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT REPEAT('abc', 3);
  repeat   
-----------
 abcabcabc
(1 row)

m_db=# SELECT REPEAT('abc', NULL);
 repeat 
--------

(1 row)

m_db=# SELECT REPEAT('abc', -1);
 repeat 
--------

(1 row)

REPLACE

REPLACE(str, from_str, to_str)

Description: Replaces the string from_str in the string str with the string to_str. Case sensitivity is required. If any parameter is NULL, NULL is returned. The length of the return value is affected by the GUC parameter max_allowed_packet. The range of the maximum value is as follows: Min(max_allowed_packet, 1073741819).

Parameters: Table 2 describes the parameters.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT REPLACE('abc,efg,hijk,abcde,dabc,e', 'abc', 'xyz');
          replace          
---------------------------
 xyz,efg,hijk,xyzde,dxyz,e
(1 row)

m_db=# SELECT REPLACE('abc,efg,hijk,abcde,dabc,e', NULL, 'xyz');
 replace 
---------

(1 row)

REVERSE

REVERSE(str)

Description: Returns str in reverse order.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT REVERSE('abcd');
 reverse 
---------
 dcba
(1 row)

RIGHT

RIGHT(str, len)

Description: Returns the rightmost len characters of str. If str or len is NULL, NULL is returned.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT RIGHT('abcdef', 4);
 right 
-------
 cdef
(1 row)

m_db=# SELECT RIGHT('abcdef', NULL);
 right 
-------

(1 row)

RPAD

RPAD(str, len, padstr)

Description: Uses padstr to pad str on the right until the length is len. The length of the return value is affected by the GUC parameter max_allowed_packet. The range of the maximum value is as follows: Min(max_allowed_packet/Maximum length of a single character in the character set returned by the function, 1073741819).

Parameters: Table 3 describes the parameters.

Return value type: TEXT or LONGBLOB

• If the length of str is greater than len, len characters on the left of str are returned.
• If str, len, or padstr is NULL, NULL is returned.
• If the value of len is negative, NULL is returned.

Examples:

m_db=#  SELECT RPAD('hello', 10, 'abc');
    rpad    
------------
 helloabcab
(1 row)

m_db=# SELECT RPAD('hello', 3, 'abc');
 rpad 
------
 hel
(1 row)

m_db=# SELECT RPAD('hello', 10, NULL);
 rpad 
------

(1 row)

m_db=# SELECT RPAD('hello', -10, 'abc');
 rpad 
------

(1 row)

RTRIM

RTRIM(str)

Description: Deletes spaces on the right of str.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT RTRIM('  hello   ');
  rtrim  
---------
   hello
(1 row)

SPACE

SPACE(N)

Description: Returns a string consisting of N spaces. If N is less than or equal to 0, an empty string is returned. The length of the return value is affected by the GUC parameter max_allowed_packet. The range of the maximum value is as follows: Min(max_allowed_packet, 1073741819).

Return value type: TEXT

Examples:

m_db=# SELECT CONCAT('a', SPACE(6), 'b');
  concat  
----------
 a      b
(1 row)

m_db=# SELECT CONCAT('a', SPACE(-1), 'b');
 concat 
--------
 ab
(1 row)

STRCMP

STRCMP(str1, str2)

Description: Checks whether str1 is the same as str2.

Return type: INT

• If str1 is equal to str2, the STRCMP function returns 0.
• If str1 is smaller than str2, the STRCMP function returns -1.
• If str1 is greater than str2, the STRCMP function returns 1.
• If any parameter is NULL, the STRCMP function returns NULL.

Examples:

m_db=# SELECT STRCMP('abc', 'abc');
 strcmp 
--------
      0
(1 row)

m_db=# SELECT STRCMP('abc1', 'abc');
 strcmp 
--------
      1
(1 row)

m_db=# SELECT STRCMP('abc', 'abc1');
 strcmp 
--------
     -1
(1 row)

m_db=# SELECT STRCMP('abc1', 'abc2');
 strcmp 
--------
     -1
(1 row)

m_db=# SELECT STRCMP('abc1', NULL);
 strcmp 
--------

(1 row)

SUBSTR

The SUBSTR function has the following four prototypes. The meanings of str, pos, and len in each prototype are the same.

• SUBSTR(str, pos)
• SUBSTR(str FROM pos)
• SUBSTR(str, pos, len)
• SUBSTR(str FROM pos FOR len)
• SUBSTR(str FOR len FROM pos)
• SUBSTR(str FOR len)

Description: Returns the sub-string of str. The start position is pos and the length is len. If len is not specified, the returned sub-string starts from pos and ends at the end of str.

• The position of pos starts from 1.
• If the value of a parameter contains NULL, NULL is returned.
• If pos is a negative number, the start position is determined from the tail of str to the head in reverse order.
• If the value of len is less than or equal to 0, the position of pos is 0, or the position of pos is out of range, an empty string is returned.

Parameters: Table 4 describes the parameters.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT SUBSTR('abcdefg', 2, 3);
 substr 
--------
 bcd
(1 row)

m_db=# SELECT SUBSTR('abcdefg', NULL);
 substr 
--------

(1 row)

m_db=# SELECT SUBSTR('abcdefg', 2);
 substr 
--------
 bcdefg
(1 row)

m_db=# SELECT SUBSTR('abcdefg', -2, 3);
 substr 
--------
 fg
(1 row)

m_db=# SELECT SUBSTR('abcdefg', -2);
 substr 
--------
 fg
(1 row)

m_db=# SELECT SUBSTR('abcdefg', 0, 3);
 substr 
--------

(1 row)

SUBSTRING

The SUBSTRING function has the following four prototypes. The meanings of str, pos, and len in each prototype are the same.

• SUBSTRING(str, pos)
• SUBSTRING(str FROM pos)
• SUBSTRING(str, pos, len)
• SUBSTRING(str FROM pos FOR len)
• SUBSTRING(str FOR len FROM pos)
• SUBSTRING(str FOR len)

Description: The function and usage of SUBSTRING are the same as those of SUBSTR.

Parameters: Table 5 describes the parameters.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT SUBSTRING('abcdefg', 2, 3);
 substring 
-----------
 bcd
(1 row)

m_db=# SELECT SUBSTRING('abcdefg', NULL);
 substring 
-----------

(1 row)

m_db=# SELECT SUBSTRING('abcdefg', 2);
 substring 
-----------
 bcdefg
(1 row)

m_db=# SELECT SUBSTRING('abcdefg', -2, 3);
 substring 
-----------
 fg
(1 row)

m_db=# SELECT SUBSTRING('abcdefg', -2);
 substring 
-----------
 fg
(1 row)

m_db=# SELECT SUBSTRING('abcdefg', 0, 3);
 substring 
-----------

(1 row)

SUBSTRING_INDEX

SUBSTRING_INDEX(str, delim, count)

Description: Uses delim and count to determine the position of the final delimiter in the string str and returns all characters on the left (or right) of the final delimiter. When delim is matched, case sensitivity is required.

• If count is a positive number, the system counts from the left of the string until delim appears for count times. All characters on the left of the final delimiter are returned.
• If count is a negative number, the system counts from the right of the string until delim appears for |count| times. All characters on the right of the final delimiter are 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.

Parameters: Table 6 describes the parameters.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT SUBSTRING_INDEX('abc..def..ghi..jkl....', '..', 3);
 substring_index 
-----------------
 abc..def..ghi
(1 row)

m_db=# SELECT SUBSTRING_INDEX('abc..def..ghi..jkl....', '..', -3);
 substring_index 
-----------------
 jkl....
(1 row)

m_db=# SELECT SUBSTRING_INDEX('abc..def..ghi..jkl....', '..', NULL);
 substring_index 
-----------------

(1 row)

m_db=# SELECT SUBSTRING_INDEX('abc..def..ghi..jkl....', '..' ,0);
 substring_index 
-----------------

(1 row)

TRIM

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

Description: Deletes all prefix/suffix remstr from str. If any parameter is NULL, NULL is returned. Matching remstr is case-sensitive.

• remstr: The default value is a space.

• [BOTH | LEADING | TRAILING]:
  • BOTH: Deletes the prefix and suffix.
  • LEADING: Deletes the prefix.
  • TRAILING: Deletes the suffix.
  • The default value is BOTH.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT TRIM('   abc   ');
 trim 
------
 abc
(1 row)

m_db=# SELECT TRIM(TRAILING FROM '   abc   ');
  trim  
--------
    abc
(1 row)

m_db=# SELECT TRIM(BOTH 'xyz' FROM 'xyzxyabcxyzxy');
    trim    
------------
 xyabcxyzxy
(1 row)

UCASE

UCASE(str)

Description: Converts lowercase letters in a string to uppercase letters. The function and usage of UCASE are the same as those of UPPER.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT UCASE('Hello,world!');
    ucase     
--------------
 HELLO,WORLD!
(1 row)

UNHEX

UNHEX(str)

Description: Interprets each character in str as a hexadecimal number, converts it to the byte represented by the number, and returns a binary string. The reverse operation is HEX(str). The character in str must be a valid hexadecimal number. If the parameter contains any invalid number, NULL is returned.

Return value type: LONGBLOB

Examples:

m_db=# SELECT UNHEX(616263), HEX(UNHEX('616263'));
 unhex |  hex   
-------+--------
 abc   | 616263
(1 row)

UPPER

UPPER(str)

Description: Converts lowercase letters in a string to uppercase letters. The function and usage of UPPER are the same as those of UCASE.

Return value type: TEXT or LONGBLOB

Examples:

m_db=# SELECT UPPER('Hello,world!');
    upper     
--------------
 HELLO,WORLD!
(1 row)