On this page

Show all

Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-2.x)/ SQL Reference/ Functions and Operators/ Character Processing Functions and Operators

Character Processing Functions and Operators

Updated on 2025-02-27 GMT+08:00

View PDF

String functions and operators provided by GaussDB are for concatenating strings with each other, concatenating strings with non-strings, and matching the patterns of strings. Note: Except length-related functions, other functions and operators of string processing functions do not support parameters of CLOB whose size is greater than 1 GB.

bit_length(string)

Description: Specifies the number of bits occupied by a string.

Return type: int

Example:

     
        openGauss=# SELECT bit_length('world');
 bit_length
------------
         40
(1 row)

btrim(string text [, characters text])
Description: Removes the longest string consisting only of characters in characters (a space by default) from the start and end of string.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT btrim('sring' , 'ing'); btrim ------- sr (1 row)

char_length(string) or character_length(string)

Description: Specifies the number of characters in a string.

Return type: int

Example:

     
        openGauss=# SELECT char_length('hello');
 char_length
-------------
           5
(1 row)

instr(text,text,int,int)

Description: instr(string1,string2,int1,int2) returns the text from int1 to int2 in string1. The first int indicates the start position for matching, and the second int indicates the number of matching times.

Return type: int

Example:

     
        openGauss=# SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
 instr
-------
     6
(1 row)

lengthb(text/bpchar)
Description: Obtains the number of bytes of a specified string.

Return type: int

Example:
1 2 3 4 5

openGauss=# SELECT lengthb('hello'); lengthb --------- 5 (1 row)
left(str text, n int)
Description: Returns the first n characters in a string. When n is negative, all but the last |n| characters are returned.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT left('abcde', 2); left ------ ab (1 row)

length(string bytea, encoding name)

Description: Specifies the number of characters in string in the given encoding. The string must be valid in this encoding.

Return type: int

Example:

     
        openGauss=# SELECT length('jose', 'UTF8');
 length
--------
      4
(1 row)

NOTE:

If the length of the bytea type is queried and UTF8 encoding is specified, the maximum length can only be 536870888.

lpad(string text, length int [, fill text])
Description: Fills up string to length by appending the characters fill (a space by default). If string is already longer than length, then it is truncated.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT lpad('hi', 5, 'xyza'); lpad ------- xyzhi (1 row)

notlike(x bytea name text, y bytea text)

Description: Compares x and y to check whether they are inconsistent.

Return type: Boolean

Example:

     
        openGauss=# SELECT notlike(1,2);
    notlike
--------------
            t
(1 row)
openGauss=# SELECT notlike(1,1);
    notlike
--------------
            f
(1 row)

octet_length(string)

Description: Specifies the number of bytes in a string.

Return type: int

Example:

     
        openGauss=# SELECT octet_length('jose');
 octet_length
--------------
            4
(1 row)

overlay(string placing string FROM int [for int])

Description: Replaces substrings. FROM int indicates the start position of the replacement in the first string. for int indicates the number of characters replaced in the first string.

Return type: text

Example:

     
        openGauss=# SELECT overlay('hello' placing 'world' from 2 for 3 );
 overlay 
---------
 hworldo
(1 row)

position(substring in string)

Description: Specifies the position of a substring. Parameters are case-sensitive.

Return type: int. If the character string does not exist, 0 is returned.

Example:

     
        openGauss=# SELECT position('ing' in 'string');
 position
----------
        4
(1 row)

pg_client_encoding()

Description: Specifies the current client encoding name.

Return type: name

Example:

     
        openGauss=# SELECT pg_client_encoding();
 pg_client_encoding
--------------------
 UTF8
(1 row)

quote_ident(string text)

Description: Returns the given string suitably quoted to be used as an identifier in an SQL statement string (quotation marks are used as required). Quotation marks are added only if necessary (that is, if the string contains non-identifier characters or would be case-folded). Embedded quotation marks are properly doubled.

Return type: text

Example:

     
        openGauss=# SELECT quote_ident('hello world');
 quote_ident
--------------
 "hello world"
(1 row)

quote_literal(string text)

Description: Returns the given string suitably quoted to be used as a string literal in an SQL statement string (quotation marks are used as required).

Return type: text

Example:

     
        openGauss=# SELECT quote_literal('hello');
 quote_literal 
---------------
 'hello'
(1 row)

If a command similar to the following exists, the text will be escaped.

     
        openGauss=# SELECT quote_literal(E'O\'hello');
 quote_literal
---------------
 'O''hello'
(1 row)

If a command similar to the following exists, the backslash will be properly doubled.

     
        openGauss=# SELECT quote_literal('O\hello');
 quote_literal 
---------------
 E'O\\hello'
(1 row)

If the parameter is null, NULL is returned. If the parameter may be null, you are advised to use quote_nullable.

     
        openGauss=# SELECT quote_literal(NULL);
 quote_literal 
---------------

(1 row)

quote_literal(value anyelement)

Description: Converts the given value to text and then quotes it as a literal.

Return type: text

Example:

     
        openGauss=# SELECT quote_literal(42.5);
 quote_literal 
---------------
 '42.5'
(1 row)

If a command similar to the following exists, the given value will be escaped.

     
        openGauss=# SELECT quote_literal(E'O\'42.5');
 quote_literal
---------------
 '0''42.5'
(1 row)

If a command similar to the following exists, the backslash will be properly doubled.

     
        openGauss=# SELECT quote_literal('O\42.5');
 quote_literal 
---------------
 E'O\\42.5'
(1 row)

quote_nullable(string text)

Description: Returns the given string suitably quoted to be used as a string literal in an SQL statement string (quotation marks are used as required).

Return type: text

Example:

     
        openGauss=# SELECT quote_nullable('hello');
 quote_nullable 
----------------
 'hello'
(1 row)

If a command similar to the following exists, the text will be escaped.

     
        openGauss=# SELECT quote_nullable(E'O\'hello');
 quote_nullable
----------------
 'O''hello'
(1 row)

If a command similar to the following exists, the backslash will be properly doubled.

     
        openGauss=# SELECT quote_nullable('O\hello');
 quote_nullable
----------------
 E'O\\hello'
(1 row)

If the parameter is null, NULL is returned.

     
        openGauss=# SELECT quote_nullable(NULL);
 quote_nullable
----------------
 NULL
(1 row)

quote_nullable(value anyelement)

Description: Converts the given value to text and then quotes it as a literal.

Return type: text

Example:

     
        openGauss=# SELECT quote_nullable(42.5);
 quote_nullable
----------------
 '42.5'
(1 row)

If a command similar to the following exists, the given value will be escaped.

     
        openGauss=# SELECT quote_nullable(E'O\'42.5');
 quote_nullable 
----------------
 'O''42.5'
(1 row)

If a command similar to the following exists, the backslash will be properly doubled.

     
        openGauss=# SELECT quote_nullable('O\42.5');
 quote_nullable
----------------
 E'O\\42.5'
(1 row)

If the parameter is null, NULL is returned.

     
        openGauss=# SELECT quote_nullable(NULL);
 quote_nullable
----------------
 NULL
(1 row)

substring_inner(string [from int] [for int])

Description: Extracts a substring. from int indicates the start position of the truncation. for int indicates the number of characters truncated.

Return type: text

Example:

     
        openGauss=# SELECT substring_inner('adcde', 2,3);
 substring_inner
-----------------
 dcd
(1 row)

substring(string [from int] [for int])

Description: Extracts a substring. from int indicates the start position of the truncation. for int indicates the number of characters truncated.

Return type: text

Example:

     
        openGauss=# SELECT substring('Thomas' from 2 for 3);
 substring
-----------
 hom
(1 row)

rawcat(raw,raw)
Description: Indicates the string concatenation function.

Return type: raw

Example:
1 2 3 4 5

openGauss=# SELECT rawcat('ab','cd'); rawcat -------- ABCD (1 row)
regexp_count(string text, pattern text [, position int [, flags text]])
Description: obtains the number of substrings used for matching.

Parameter description:
- string: source character string used for matching.
- pattern: regular expression pattern string used for matching.
- position: sequence number of the character to be matched from the source character string. This parameter is optional. The default value is 1.
- flags: contains zero or multiple single-letter flags that change the matching behavior of the function. This parameter is optional.
Return type: int

Example:
```
openGauss=# SELECT regexp_count('foobarbaz','b(..)', 5) AS RESULT;
result
--------
1
(1 row)
```
regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]])
Description: obtains the position (starting from 1) of the substring that meets the matching condition. If no substring is matched, 0 is returned.

Parameter description:
- string: source character string used for matching.
- pattern: regular expression pattern string used for matching.
- position: start character of the source string used for matching. This parameter is optional. The default value is 1.
- occurrence: sequence number of the matched substring to be replaced. This parameter is optional. The default value is 1.
- return_opt: specifies whether to return the position of the first or last character of the matched substring. This parameter is optional. If the value is 0, the position of the first character (starting from 1) of the matched substring is returned. If the value is greater than 0, the position of the next character of the end character of the matched substring is returned. The default value is 0.
- flags: contains zero or multiple single-letter flags that change the matching behavior of the function. This parameter is optional.
Return type: int

Example:
```
openGauss=# SELECT regexp_instr('foobarbaz','b(..)', 1, 1, 0) AS RESULT;
result
--------
4
(1 row)

openGauss=# SELECT regexp_instr('foobarbaz','b(..)', 1, 2, 0) AS RESULT;
result
--------
7
(1 row)
```
repeat(string text, number int)
Description: Repeats string the specified number of times.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT repeat('Pg', 4); repeat ---------- PgPgPgPg (1 row)
NOTE:

The maximum size of memory allocated at a time cannot exceed 1 GB due to the memory allocation mechanism of the database. Therefore, the maximum value of number cannot exceed (1 GB – x)/lengthb (string) – 1. x indicates the length of the header information, which is usually greater than 4 bytes. The value varies among different scenarios.

replace(string text, from text, to text)

Description: Replaces all occurrences in string of substring from with substring to.

Return type: text

Example:

     
        openGauss=# SELECT replace('abcdefabcdef', 'cd', 'XXX');
    replace     
----------------
 abXXXefabXXXef
(1 row)

replace(string, substring)

Description: Deletes all substrings in a string.

String type: text

Substring type: text

Return type: text

Example:

     
        openGauss=# SELECT replace('abcdefabcdef', 'cd');
    replace     
----------------
 abefabef
(1 row)

reverse(str)

Description: Returns the reversed string.

Return type: text

Example:

     
        openGauss=# SELECT reverse('abcde');
 reverse
---------
 edcba
(1 row)

right(str text, n int)

Description: Returns the last n characters in a string. When n is negative, all but the first |n| characters are returned.

Return type: text

Example:

     
        openGauss=# SELECT right('abcde', 2);
 right
-------
 de
(1 row)

openGauss=# SELECT right('abcde', -2);
 right 
-------
 cde
(1 row)

rpad(string text, length int [, fill text])
Description: Fills up string to length by appending the characters fill (a space by default). If string is already longer than length, then it is truncated.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT rpad('hi', 5, 'xy'); rpad ------- hixyx (1 row)
rtrim(string text [, characters text])
Description: Removes the longest string containing only characters from characters (a space by default) from the end of string.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT rtrim('trimxxxx', 'x'); rtrim ------- trim (1 row)
substrb(text,int,int)
Description: Extracts a substring. The first int indicates the start position of the subtraction. The second int indicates the number of characters subtracted.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT substrb('string',2,3); substrb --------- tri (1 row)
substrb(text,int)
Description: Extracts a substring. int indicates the start position of the extraction.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT substrb('string',2); substrb --------- tring (1 row)
substr(bytea,from,count)
Description: Extracts a substring from bytea. from specifies the position where the extraction starts. count specifies the length of the extracted substring.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT substr('string',2,3); substr -------- tri (1 row)

string || string

Description: Concatenates strings.

Return type: text

Example:

     
        openGauss=# SELECT 'MPP'||'DB' AS RESULT;
 result 
--------
 MPPDB
(1 row)

string || non-string or non-string || string

Description: Concatenates strings and non-strings.

Return type: text

Example:

     
        openGauss=# SELECT 'Value: '||42 AS RESULT;
  result   
-----------
 Value: 42
(1 row)

split_part(string text, delimiter text, field int)

Description: Splits string on delimiter and returns the fieldth column (counting from text of the first appeared delimiter).

Return type: text

Example:

     
        openGauss=# SELECT split_part('abc~@~def~@~ghi', '~@~', 2);
 split_part
------------
 def
(1 row)

strpos(string, substring)

Description: Specifies the position of a substring. It is the same as position(substring in string). However, the parameter sequences of them are reversed.

Return type: int

Example:

     
        openGauss=# SELECT strpos('source', 'rc');
 strpos
--------
      4
(1 row)

to_hex(number int or bigint)

Description: Converts a number to a hexadecimal expression.

Return type: text

Example:

     
        openGauss=# SELECT to_hex(2147483647);
  to_hex
----------
 7fffffff
(1 row)

translate(string text, from text, to text)

Description: Any character in string that matches a character in from is replaced by the corresponding character in to. If from is longer than to, extra characters occurred in from are removed.

Return type: text

Example:

     
        openGauss=# SELECT translate('12345', '143', 'ax');
 translate
-----------
 a2x5
(1 row)

length(string)
Description: Obtains the number of characters in a string.

Return type: integer

Example:
1 2 3 4 5

openGauss=# SELECT length('abcd'); length -------- 4 (1 row)

lengthb(string)

Description: Obtains the number of characters in a string. The value depends on character sets (GBK and UTF8).

Return type: integer

Example:

     
        openGauss=# SELECT lengthb('Chinese');
 lengthb 
---------
       7
(1 row)

substr(string,from)
Description:

Extracts substrings from a string.

from indicates the start position of the extraction.
- If from starts at 0, the value 1 is used.
- If the value of from is positive, all characters from from to the end are extracted.
- If the value of from is negative, the last n characters in the string are extracted, and n indicates the absolute value of from.
Return type: text

Example:

If the value of from is positive:
1 2 3 4 5

openGauss=# SELECT substr('ABCDEF',2); substr -------- BCDEF (1 row)
If the value of from is negative:
1 2 3 4 5

openGauss=# SELECT substr('ABCDEF',-2); substr -------- EF (1 row)
substr(string,from,count)
Description:

Extracts substrings from a string.

from indicates the start position of the extraction.

count indicates the length of the extracted substring.
- If from starts at 0, the value 1 is used.
- If the value of from is positive, extract count characters starting from from.
- If the value of from is negative, extract the last n count characters in the string, in which n indicates the absolute value of from.
- If the value of count is smaller than 1, null is returned.
Return type: text

Example:

If the value of from is positive:
1 2 3 4 5

openGauss=# SELECT substr('ABCDEF',2,2); substr -------- BC (1 row)
If the value of from is negative:
1 2 3 4 5

openGauss=# SELECT substr('ABCDEF',-3,2); substr -------- DE (1 row)
substrb(string,from)
Description: The functionality of this function is the same as that of SUBSTR(string,from). However, the calculation unit is byte.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT substrb('ABCDEF',-2); substrb --------- EF (1 row)
substrb(string,from,count)
Description: The functionality of this function is the same as that of SUBSTR(string,from,count). However, the calculation unit is byte.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT substrb('ABCDEF',2,2); substrb --------- BC (1 row)

trim([leading |trailing |both] [characters] from string)

Description: Removes the longest string containing only the characters (a space by default) from the start/end/both ends of the string.

Return type: text

Example:

     
        openGauss=# SELECT trim(BOTH 'x' FROM 'xTomxx');
 btrim
-------
 Tom
(1 row)

     
        openGauss=# SELECT trim(LEADING 'x' FROM 'xTomxx');
 ltrim
-------
 Tomxx
(1 row)

     
        openGauss=# SELECT trim(TRAILING 'x' FROM 'xTomxx');
 rtrim
-------
 xTom
(1 row)

rtrim(string [, characters])
Description: Removes the longest string containing only characters from characters (a space by default) from the end of string.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT rtrim('TRIMxxxx','x'); rtrim ------- TRIM (1 row)
ltrim(string [, characters])
Description: Removes the longest string containing only characters from characters (a space by default) from the start of string.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT ltrim('xxxxTRIM','x'); ltrim ------- TRIM (1 row)
upper(string)
Description: Converts the string into the uppercase.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT upper('tom'); upper ------- TOM (1 row)
lower(string)
Description: Converts the string into the lowercase.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT lower('TOM'); lower ------- tom (1 row)
rpad(string varchar, length int [, fill varchar])
Description: Fills up string to length by appending the characters fill (a space by default). If string is already longer than length, then it is truncated.

length in GaussDB indicates the character length. One Chinese character is counted as one character.

Return type: text

Example:
1 2 3 4 5

openGauss=# SELECT rpad('hi',5,'xyza'); rpad ------- hixyz (1 row)
1 2 3 4 5

openGauss=# SELECT rpad('hi',5,'abcdefg'); rpad ------- hiabc (1 row)

instr(string,substring[,position,occurrence])

Description: Queries and returns the value of the substring position that occurs the occurrence (1 by default) times from the position (1 by default) in the string.

If the value of position is 0, 0 is returned.
If the value of position is negative, the search is performed backwards from the last nth character in the string, in which n indicates the absolute value of position.

In this function, the calculation unit is character. One Chinese character is one character.

Return type: integer

Example:

     
        openGauss=# SELECT instr('corporate floor','or', 3);
 instr 
-------
     5
(1 row)

     
        openGauss=# SELECT instr('corporate floor','or',-3,2);
 instr 
-------
     2
(1 row)

initcap(string)

Description: Capitalizes the first letter of each word in a string.

Return type: text

Example:

     
        openGauss=# SELECT initcap('hi THOMAS');
  initcap
-----------
 Hi Thomas
(1 row)

ascii(string)
Description: Indicates the ASCII code of the first character in the string.

Return type: integer

Example:
1 2 3 4 5

openGauss=# SELECT ascii('xyz'); ascii ------- 120 (1 row)

replace(string varchar, search_string varchar, replacement_string varchar)

Description: Replaces all search_string in the string with replacement_string.

Return type: text

Example:

     
        openGauss=# SELECT replace('jack and jue','j','bl');
    replace     
----------------
 black and blue
(1 row)

lpad(string varchar, length int[, repeat_string varchar])

Description: Adds a series of repeat_string (a space by default) on the left of the string to generate a new string with the total length of n.

If the length of the string is longer than the specified length, the function truncates the string and returns the substrings with the specified length.

Return type: varchar

Example:

     
        openGauss=# SELECT lpad('PAGE 1',15,'*.');
      lpad       
-----------------
 *.*.*.*.*PAGE 1
(1 row)

     
        openGauss=# SELECT lpad('hello world',5,'abcd');
 lpad  
-------
 hello
(1 row)

concat(str1,str2)

Description: Concatenates str1 and str2 and returns the concatenated string. If str1 or str2 is set to NULL, NULL is returned. Note: concat calls the output function of the data type and the return value is uncertain. As a result, the optimizer cannot calculate the result in advance when generating a plan. If there are performance requirements, you are advised to use the operator ||.

NOTE:

If sql_compatibility is set to 'B' and str1 or str2 is set to NULL, the returned result is NULL.
The return value of the concat function is of the variable-length type. When the concat function is compared with table data, the character string length is lost in the combination result. As a result, the comparison results are different.

Return type: text

Example:

     
        openGauss=# SELECT concat('Hello', ' World!');
    concat    
--------------
 Hello World!
(1 row)
openGauss=# SELECT concat('Hello', NULL);
 concat
--------
 Hello
(1 row)

chr(integer)
Description: Specifies the character of the ASCII code.

Return type: varchar

Example:
1 2 3 4 5

openGauss=# SELECT chr(65); chr ----- A (1 row)

concat_ws(sep text, str"any" [, str"any" [, ...] ])

Description: Uses the first parameter as the separator, which is associated with all following parameters. The NULL parameter is ignored.

NOTICE:

If the first parameter value is NULL, the returned result is NULL.
If the first parameter is provided but the parameter value is an empty string ('') and the SQL compatibility mode of the database is set to A, the returned result is NULL. This is because the A-compatible mode treats the empty string ('') as NULL. To resolve this problem, you can change the SQL compatibility mode of the database to B, C, or PG.

Return type: text

Example:

     
        openGauss=# SELECT concat_ws(',', 'ABCDE', 2, NULL, 22);
 concat_ws
------------
 ABCDE,2,22
(1 row)

nlssort(string text, sort_method text)

Description: Returns the encoding value of a string in the sorting mode specified by sort_method. The encoding value can be used for sorting and determines the sequence of the string in the sorting mode. Currently, sort_method can be set to nls_sort=schinese_pinyin_m or nls_sort=generic_m_ci. nls_sort=generic_m_ci supports only the case-insensitive order for English characters.

String type: text

sort_method type: text

Return type: text

Example:

     
        openGauss=# CREATE TABLE test(a text);

openGauss=# INSERT into test(a) values ('abC');

openGauss=# insert INTO test(a) VALUES ('abC');

openGauss=# INSERT INTO test(a) VALUES ('abc');

openGauss=# SELECT * FROM test ORDER BY nlssort(a,'nls_sort=schinese_pinyin_m');
   a    
--------
 abc
 abC
 abC
(3 rows)

openGauss=# SELECT * FROM test ORDER BY nlssort(a, 'nls_sort=generic_m_ci');
   a    
--------
 abC
 abc
 abC
(3 rows)

openGauss=# DROP TABLE test;

convert(string bytea, src_encoding name, dest_encoding name)

Description: Converts the string to dest_encoding. src_encoding specifies the source code encoding. The string must be valid in this encoding.

Return type: bytea

Example:

     
        openGauss=# SELECT convert('text_in_utf8', 'UTF8', 'GBK');
          convert        
----------------------------
 \x746578745f696e5f75746638
(1 row)

NOTE:

If the rule for converting between source to target encoding (for example, GBK and LATIN1) does not exist, the string is returned without conversion. See the pg_conversion system catalog for details.

Example:

       
        
          
          openGauss=# SHOW server_encoding;
 server_encoding 
-----------------
 LATIN1
(1 row)

openGauss=# SELECT convert_from('some text', 'GBK');
 convert_from 
--------------
 some text
(1 row)

db_latin1=# SELECT convert_to('some text', 'GBK');
      convert_to      
----------------------
 \x736f6d652074657874
(1 row)

db_latin1=# SELECT convert('some text', 'GBK', 'LATIN1');
       convert        
----------------------
 \x736f6d652074657874
(1 row)

         

       
      

convert_from(string bytea, src_encoding name)

Description: Converts a string using the coding mode of the database.

src_encoding specifies the source code encoding. The string must be valid in this encoding.

Return type: text

Example:

     
        openGauss=# SELECT convert_from('text_in_utf8', 'UTF8');
 convert_from
--------------
 text_in_utf8
(1 row)

convert_to(string text, dest_encoding name)

Description: Converts a string to dest_encoding.

Return type: bytea

Example:

     
        openGauss=# SELECT convert_to('some text', 'UTF8');
      convert_to
----------------------
encode \x736f6d652074657874
(1 row)

format(formatstr text [, str"any" [, ...] ])

Description: Formats a string.

Return type: text

Example:

     
        openGauss=# SELECT format('Hello %s, %1$s', 'World');
       format       
--------------------
 Hello World, World
(1 row)

md5(string)

Description: Encrypts a string in MD5 mode and returns a value in hexadecimal form.

NOTE:

The MD5 encryption algorithm is not recommended because it has lower security and poses security risks.

Return type: text

Example:

     
        openGauss=# SELECT md5('ABC');
               md5                
----------------------------------
 902fbdd2b1df0c4f70b4a5d23525e932
(1 row)

decode(string text, format text)

Description: Decodes binary data from textual representation.

Return type: bytea

Example:

     
        openGauss=# SELECT decode('MTIzAAE=', 'base64');
    decode    
--------------
 \x3132330001
(1 row)

encode(data bytea, format text)

Description: Encodes binary data into a textual representation.

Return type: text

Example:

     
        openGauss=# SELECT encode(E'123\\000\\001', 'base64');
  encode  
----------
 MTIzAAE=
(1 row)

NOTE:

For a string containing newline characters, for example, a string consisting of a newline character and a space, the value of length and lengthb in GaussDB is 2.
In GaussDB, n in the CHAR(n) type indicates the number of characters. Therefore, for multiple-octet coded character sets, the length returned by the LENGTHB function may be longer than n.
GaussDB supports multiple types of databases, and the value can be A, B, C, or PG. If the database type is not specified, A is used by default. In this case, the lexical analyzer is different from that of the other three databases, which considers an empty character string as NULL. Therefore, when A is specified, if an empty string is used as a parameter in the preceding character operation function, no output is displayed. For example:
```
openGauss=# SELECT translate('12345','123','');
translate  
-----------

(1 row)
```
This is because the kernel checks whether the input parameter contains NULL before calling the corresponding function. If the input parameter contains NULL, the kernel does not call the corresponding function. As a result, no output is displayed. If PG is specified, the processing of character strings is the same as that of PostgreSQL. Therefore, the preceding problem does not occur.

Parent topic: Functions and Operators

Previous topic: Comparison Operators

Next topic: Binary String Functions and Operators

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