Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ SQL Reference/ Functions and Operators/ Binary String Functions and Operators/ String Operators
Updated on 2025-08-19 GMT+08:00
View PDF

String Operators

SQL defines some string functions that use keywords, rather than commas, to separate arguments.

octet_length(string)

Description: Specifies the number of bytes in a binary string.

Return type: int

Example:

1
2
3
4
5
 
gaussdb=# SELECT octet_length(E'jo\\000se'::bytea) AS RESULT;
 result
--------
      5
(1 row)

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

Description: Replaces substrings.

Return type: bytea

Example:

1
2
3
4
5
 
gaussdb=# SELECT overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) AS RESULT;
     result     
----------------
 \x5402036d6173
(1 row)

position(substring in string)

Description: Specifies the position of a specified substring.

Return type: int

Example:

1
2
3
4
5
 
gaussdb=# SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT;
 result
--------
      3
(1 row)

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

Description: Truncates a substring.

Return type: bytea

Example:

1
2
3
4
5
 
gaussdb=# SELECT substring(E'Th\\000omas'::bytea from 2 for 3) AS RESULT; 
  result  
----------
 \x68006f
(1 row)

substr(string, from int [, for int])

Description: Truncates a substring.

Return type: bytea

Example:

1
2
3
4
5
 
gaussdb=# select substr(E'Th\\000omas'::bytea,2, 3) as result;
  result
----------
 \x68006f
(1 row)

trim([both] bytes from string)

Description: Removes the longest string containing only bytes from the start and end of string.

Return type: bytea

Example:

1
2
3
4
5
 
gaussdb=# SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) AS RESULT;
  result  
----------
 \x546f6d
(1 row)