Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ Functions and Operators/ String Functions

Updated on 2025-10-23 GMT+08:00

View PDF

String Functions

M-compatible databases provide string functions to process strings.

For a function that operates on the position of a string, the number of the first position is 1.
For functions that use the length as the parameter, the non-integer parameter is rounded off to the nearest integer.
If the character set of the database is SQL_ASCII, the database interprets byte values 0 to 127 according to the ASCII standard, and byte values 128 to 255 are regarded as characters that cannot be parsed. If SQL_ASCII is used, the database cannot convert or verify non-ASCII characters. As a result, string functions such as CHAR_LENGTH, FIND_IN_SET, LEFT, LOWER, REVERSE, RIGHT, STRCMP, and TRIM generate unexpected results. If any non-ASCII data is used, you are advised not to set the character set of the database to SQL_ASCII.

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)

BIN

BIN(INT N)

Description: Returns a binary value of N in string format. This functions the same as CONV(N,10,2).

Return value type: TEXT

Examples:

m_db=# SELECT BIN(12);
 bin  
------
 1100
(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

CHAR(INT N,... [USING charset_name])

Description: Converts each parameter N into a character using ASCII codes. charset_name indicates the character set name.

Return type: TEXT or BLOB

Examples:

m_db=# SELECT CHAR(71,97,117,115,115);
 char  
-------
 Gauss
(1 row)

m_db=# SELECT CHAR(71,97,117,115,115 using utf8);
 char  
-------
 Gauss
(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)

COMPRESS

COMPRESS (TEXT string_to_compress)

Description: Compresses a string and returns the result of the binary string type.

Return value type: LONGBLOB

Examples:

m_db=# SELECT HEX(COMPRESS('abcde'));
       HEX(COMPRESS('abcde'))       
------------------------------------
 05000000789C4B4C4A4E49050005C801F0
(1 row)

CONCAT

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

Description: Applies to multiple strings, concatenates them in sequence, and returns the concatenated string. If any string is NULL, NULL is returned.

Return type: TEXT or BLOB

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 will be ignored.

Return type: TEXT or BLOB

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)

ELT

ELT(INT N, TEXT str1, TEXT str2, TEXT str3, ...)

Description: Returns the element at a position specified by N from a string list. NULL is returned if N is empty, less than 1, or greater than the total number of character strings, or the character string at N is NULL.

Parameters:

N: specifies the position.

str1, str2, str3, ...: specifies a string parameter list. The maximum number of parameters is 8192.

Return type: TEXT or BLOB

Examples:

m_db=# SELECT ELT (1,'a');
 elt 
-----
 a
(1 row)

m_db=# SELECT ELT (2,'a', 'b');
 elt 
-----
 b
(1 row)

EXPORT_SET

EXPORT_SET (INT bits, TEXT on, TEXT off[, TEXT separator[, INT length]])

Description: Generates a string concatenated by a specified separator based on the binary bits of a parameter. If length is not specified or is not in the range [0,64], the length is 64 bits. Bits are converted into binary values. If the bit is 1, the character string specified by on will be used. Conversely, if the bit is 0, the character string specified by off will be used. The character string will be separated by the specified separator. However, if no separator is specified, commas (,) will be used as the default separator. 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: See Table 1.

**Table 1** Parameters of the EXPORT_SET function
Name	Description
bits	Required. It specifies a number. A bit value determines the position of on or off from right to left.
on	Required. It specifies a character string used when the corresponding bit is 1.
off	Required. It specifies a character string used when the corresponding bit is 0.
separator	Optional. It specifies a separator. A comma (,) is used by default.
length	Optional. It specifies the number of elements in a set. The default value is 64.

Return value type: TEXT

Examples:

m_db=# SELECT EXPORT_SET(5, 'Y', 'N');
                                                           export_set                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Y,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N

m_db=# SELECT EXPORT_SET(5,'Y','N','|');
                                                           export_set                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Y|N|Y|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N
(1 row)

m_db=# SELECT EXPORT_SET(5,'Y','N','|', 5);
 export_set 
------------
 Y|N|Y|N|N
(1 row)

FIELD

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

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

Return type: bigint

If str is NULL or is not in the str1,str2,str3,... list, 0 is returned.
If the input parameters of the function are all digits, the comparison is performed based on digits. If the input parameters are all non-digits, the comparison is performed based on character strings. If the input parameters contain both digits and non-digits, the comparison is performed based on the double type.

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');
 field 
------
     2
(1 row)

FIND_IN_SET

FIND_IN_SET(str, strlist)

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

Return type: INT

If strlist does not contain str, 0 is returned.
If str or strlist is NULL, NULL is returned.
If str contains commas (,), 0 is returned.

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)

FORMAT

FORMAT(INT X , INT D [, TEXT locale])

Description: Formats the number specified by X to (#,###,###.##), rounds it off to decimal places specified by D, and returns the result as a character string. If D is set to 0, the result has no decimal part. If X or D is NULL, the result NULL is returned.

Parameters:

X: indicates the number to be formatted, which is required.

D: indicates the number of decimal places to be reserved, which is required.

locale: indicates a specific language environment, which is optional.

Return value type: TEXT

When m_format_dev_version is 's2' or later and m_format_behavior_compat_options includes 'enable_conflict_funcs', this function will take on the behavior implemented by M-compatible databases as explained here. Otherwise, it will follow the behavior specified for the ceil function in "SQL Reference > Functions and Operators > Arithmetic Functions and Operators" in Developer Guide.
In a non-M-compatible database implementation, if the input parameter type is unique to M-compatible databases (for example, BIGINT UNSIGNED), using this function may result in an exception.

Examples:

m_db=# SELECT FORMAT(12332.123456, 4);
   format    
-------------
 12,332.1235
(1 row)

m_db=# SELECT FORMAT(12332.123456,4,'de_DE');
   format    
-------------
 12.332,1235
(1 row)

FROM_BASE64

FROM_BASE64(TEXT str)

Description: Decodes the string returned through TO_BASE64() and returns the result as a binary string. 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: LONGBLOB

Examples:

m_db=# SELECT FROM_BASE64('R2F1c3M=');
 from_base64 
-------------
 Gauss
(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)
```

INSERT

INSERT(str,pos,len,newstr)

Description: Uses the content specified by newstr to replace the content of the length specified by len from the position specified by pos in str and then outputs the content.

Parameter	Type	Description
str	TEXT	Original string.
pos	INT	Start position of replacement.
len	INT	Replacement length.
newstr	TEXT	Substring to be replaced.

Return type: VARCHAR/VARBINARY/LONGTEXT/LONBLOB

Examples:

m_db=# SELECT INSERT('abcdefg',2,3,'mmm');
 insert  
---------
 ammmefg
(1 row)

-- If any of the input parameters is NULL, the function returns NULL.
m_db=# SELECT INSERT(NULL, 2, 4, 'yyy');
 insert
--------
(1 row)

-- If the location information is abnormal, the original character string is returned.
m_db=# SELECT INSERT('abcdefg', -100, 4, 'yyy');
 insert 
---------
 abcdefg
(1 row)

INSTR

INSTR(str,substr)

Description: Returns the position where substr appears for the first time in str.

Parameter	Type	Description
str	TEXT	Original string.
substr	TEXT	Substring to be searched for.

Return type: INT

Examples:

m_db=# SELECT INSTR('absbbsod','bs');
 instr('absbbsod','bs') 
------------------------
                      2
(1 row)

-- If the input parameter contains NULL, the function returns NULL.
m_db=# SELECT INSTR('abcd12abc',null);
 instr
-------
(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 type: TEXT or BLOB

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 type: TEXT or BLOB

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)

LOCATE

LOCATE(TEXT substr, TEXT str[, INT pos])

Description: Returns the position where a string specified by substr appears for the first time in a string specified by str, starting from a position specified by pos (1 by default). If substr cannot be matched in str, 0 is returned.

Parameters:

substr (required): substring to be searched for.

str (required): character string to be searched for.

pos (optional): start position of the search.

Return type: BIGINT

Examples:

m_db=# SELECT LOCATE('bar', 'foobarbar');
 locate 
--------
      4
(1 row)

m_db=# SELECT LOCATE('bar', 'foobarbar',5);
 locate 
--------
      7
(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 type: TEXT or BLOB

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).

For details about the parameters, see the following table.

Parameter	Type	Description
str	TEXT	String to be padded.
len	INT	Length of the string after padding.
padstr	TEXT	String used for padding.

Return type: TEXT or BLOB

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 type: TEXT or BLOB

Examples:

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

MAKE_SET

MAKE_SET(INT bits, TEXT str1, TEXT str2,...)

Description: Returns a string that separates substrings. The string consists of strings in the corresponding positions in the parameter set. str1 corresponds to bit 0, and str2 corresponds to bit 1. The function can process a maximum of 64 str input parameters. If there are more than 64 str input parameters, only the first 64 input parameters are retained. bits obtains the value of the rightmost N bits.

Return value type: TEXT

Examples:

m_db=# SELECT MAKE_SET(5,'hello','nice','world');
  make_set   
-------------
 hello,world
(1 row)

MD5

MD5(TEXT str)

Description: Calculates the MD5 digest of a given string and returns the result as a 32-bit hexadecimal string. If the parameter is NULL, NULL is returned.

Return value type: TEXT

Examples:

m_db=# SELECT MD5('Gauss');
               md5                
----------------------------------
 edc41fb7bf8bdac012523d1bcd949a4f
(1 row)

The MD5 encryption algorithm is not recommended because it has lower security and poses security risks.
The MD5 function records hash plaintext in logs. Therefore, you are advised not to use this function to encrypt sensitive information such as keys.

MID

MID(TEXT str, INT pos)

Description: Outputs a string starting from a position specified by pos to the end of str. The MID function is the same as the SUBSTR function, and the SUBSTR function is used internally. During upgrade observation, the MID function is called to create a view. After the rollback, the SUBSTR function is not deleted. As a result, the view is not deleted.

Return type: TEXT or BLOB

Examples:

m_db=# SELECT MID('foobarbarfoobar', 4);
     mid      
--------------
 barbarfoobar
(1 row)

MID(str FROM pos)

Description: Outputs the string starting from the position specified by pos to the end.

Return type: TEXT or BLOB

Examples:

m_db=# SELECT MID('foobarbarfoobar' FROM 4);
     mid      
--------------
 barbarfoobar
(1 row)

MID(TEXT str, INT pos, INT len)

Description: Outputs the string of the length specified by len from the position specified by pos in str.

Return type: TEXT or BLOB

Examples:

m_db=# SELECT MID('foobarbarfoobar', 4, 9);
    mid    
-----------
 barbarfoo
(1 row)

MID(str FROM pos FOR len)

Description: Outputs the string of the length specified by len from the position specified by pos in str.

Return type: TEXT or BLOB

Examples:

m_db=# SELECT MID('foobarbarfoobar' FROM 4 FOR 9);
    mid    
-----------
 barbarfoo
(1 row)

ORD

ORD(TEXT str)

Description: Returns the character code of the first character in a string parameter.

If the first character is a single-byte character, the function returns the ASCII value of the character.
If the first character is a multi-byte character, the return logic is as follows: Code of the first byte + Code of the second byte x 256 + Code of the third byte x 256 x 256, that is, result += char[n] << (8*n), where n is the byte index.

Return type: BIGINT

Examples:

m_db=# SELECT ORD('gauss');
 ord 
-----
 103
(1 row)

OCTET_LENGTH

OCTET_LENGTH(str)

Description: Returns the length of string str in bytes, that is, the number of bytes. If str is not of the string type, the length of the string in the standard output format is used.

Return type: BIGINT

Examples:

m_db=# SELECT OCTET_LENGTH('text');
 octet_length 
--------------
            4
(1 row)

POSITION

POSITION(substr in str)

Description: Returns the position where a string specified by substr appears for the first time in a string specified by str. This function has the same function as LOCATE(substr,str).

Return type: BIGINT

When m_format_dev_version is 's2' or later and m_format_behavior_compat_options includes 'enable_conflict_funcs', this function will take on the behavior implemented by M-compatible databases as explained here. Otherwise, it will follow the behavior specified for the ceil function in "SQL Reference > Functions and Operators > Arithmetic Functions and Operators" in Developer Guide.
In a non-M-compatible database implementation, if the input parameter type is unique to M-compatible databases (for example, BIGINT UNSIGNED), using this function may result in an exception.

Examples:

m_db=# SELECT POSITION('bs' in 'absbbsod');
 position 
----------
        2
(1 row)

QUOTE

QUOTE(TEXT str)

Description: Encloses str in single quotation marks for output. In addition, special characters or Control+Z (\Z) in str can be escaped. The special characters include two backslashes (\\) and a backslash and a single quotation mark (\'). If the parameter is NULL, the return value is NULL without single quotation marks.

Return type: VARCHAR/LONGTEXT/VARBINARY/LONGBLOB

Examples:

m_db=# SELECT QUOTE('Do not do that.');
       quote
-------------------
 'Do not do that.'
(1 row)
-- The character string contains two backslashes (\\), a backslash and a single quotation mark (\'), or Control+Z (\Z).
m_db=# SELECT QUOTE('Don\\t do that.');
       quote
-------------------
 'Don\\t do that.'
(1 row)

m_db=# select quote('Don\Zt do that.');
       quote
-------------------
 'Don\Zt do that.'
(1 row)

m_db=# select quote('Don\'t do that.');
       quote
-------------------
 'Don\'t do that.'
(1 row)

m_db=# SELECT QUOTE(null);
 quote 
-------
 NULL
(1 row)

RANDOM_BYTES

RANDOM_BYTES(INT len)

Description: Returns a random string of the length specified by len. If the input parameter is NULL, NULL is returned. If the input parameter value is not in [1,1024], the error information "Length value is out of range" is returned.

Return type: BLOB

Examples:

m_db=# SELECT RANDOM_BYTES(1);
 random_bytes 
--------------
 q
(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 type: TEXT or BLOB

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).

For details about the parameters, see the following table.

Parameter	Type	Description
str	TEXT	String to be replaced.
from_str	TEXT	Target search.
to_str	TEXT	Replaced to.

Return type: TEXT or BLOB

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 type: TEXT or BLOB

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 type: TEXT or BLOB

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).

For details about the parameters, see the following table.

Parameter	Type	Description
str	TEXT	String to be padded.
len	INT	Length of the string after padding.
padstr	TEXT	String used for padding.

Return type: TEXT or BLOB

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 type: TEXT or BLOB

Examples:

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

SHA

SHA(str)

Description: Calculates the SHA-1 160-bit checksum of string str.

Parameter: str, which is a string or number.

Return value: string. The value is a string of 40 hexadecimal digits.

Examples:

m_db=# SELECT SHA('abc');
                   sha                    
------------------------------------------
 a9993e364706816aba3e25717850c26c9cd0d89d
(1 row)

The SHA-1 encryption algorithm is not recommended because it has lower security and poses security risks.
The SHA function records hash plaintext in logs. Therefore, you are advised not to use this function to encrypt sensitive information such as keys.

SHA1

SHA1(str)

Description: SHA-1 is the alias of the SHA function and has the same function and usage.

Examples:

m_db=# SELECT SHA1('abc');
                   sha1                   
------------------------------------------
 a9993e364706816aba3e25717850c26c9cd0d89d
(1 row)

The SHA-1 encryption algorithm is not recommended because it has lower security and poses security risks.
The SHA-1 function records hash plaintext in logs. Therefore, you are advised not to use this function to encrypt sensitive information such as keys.

SHA2

SHA2(str, hash_length)

Description: Calculates the SHA-2 checksum of string str.

Parameters:

str: character string or number.

hash_length: corresponds to an SHA-2 algorithm. The value can be 0 (SHA-256), 224 (SHA-224), 256 (SHA-256), 384 (SHA-384), or 512 (SHA-512). For other values, NULL is returned.

Return value: string. The value is a string of 40 hexadecimal digits.

Examples:

m_db=# SELECT SHA2('abc', 224);
                           sha2                           
----------------------------------------------------------
 23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
(1 row)

The SHA-224 encryption algorithm is not recommended because it has lower security and poses security risks.
The SHA-2 function records hash plaintext in logs. Therefore, you are advised not to use this function to encrypt sensitive information such as keys.

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.

For details about the parameters, see the following table.

Parameter	Type	Description
str	TEXT	String to be truncated.
pos	INT	Start position.
len	INT	Length of a sub-string.

Return type: TEXT or BLOB

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.

For details about the parameters, see the following table.

Parameter	Type	Description
str	TEXT	String to be truncated.
pos	INT	Start position.
len	INT	Length of a sub-string.

Return type: TEXT or BLOB

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.

For details about the parameters, see the following table.

Parameter	Type	Description
str	TEXT	String to be truncated.
delim	TEXT	Delimiter.
count	INT	Number of occurrences of delimiters.

Return type: TEXT or BLOB

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)

TO_BASE64

TO_BASE64(TEXT str)

Description: Converts a string into Base64 format and returns the result as a string. If the parameter is not a character string, convert it to a character string first. 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 type: LONGTEXT

Examples:

m_db=# SELECT TO_BASE64('Gauss');
 to_base64 
-----------
 R2F1c3M=
(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 type: TEXT or BLOB

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 type: TEXT or BLOB

Examples:

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

UNCOMPRESS

UNCOMPRESS (LONGBLOB string_to_uncompress)

Description: Decompresses a string compressed by the COMPRESS function. If the input parameter is not a compressed value, NULL is returned.

Return value type: LONGBLOB

Examples:

m_db=# SELECT UNCOMPRESS(COMPRESS('abcde'));
 UNCOMPRESS(COMPRESS('abcde')) 
-------------------------------
 abcde
(1 row)

UNCOMPRESSED_LENGTH

UNCOMPRESSED_LENGTH(LONGBLOB compressed_string)

Description: Returns the length of a compressed string before compression.

Return type: INT

Examples:

m_db=# SELECT UNCOMPRESSED_LENGTH(COMPRESS('abcde'));
 UNCOMPRESSED_LENGTH(COMPRESS('abcde')) 
----------------------------------------
                                      5
(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 type: TEXT or BLOB

Examples:

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

Parent topic: Functions and Operators

Previous topic: Numeric Operation Functions and Arithmetic Operators

Next topic: Datetime Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot