Updated on 2026-11-12 GMT+08:00

Binary String Functions and Operators

During data processing in the data warehouse, users often need to perform operations on binary string (bytea type) data. For example, when processing fields that contain special characters or non-text data, users need to extract binary substrings, find the position of a specific byte sequence, replace part of the content, or perform accurate operations on binary data bit by bit or byte by byte. In addition, data imported from external systems may contain composite strings separated by delimiters, and substrings need to be extracted based on the delimiters.

The SQL standard defines some binary string functions, which use keywords (such as from, placing, and in) instead of commas to separate parameters. DWS provides more extended functions that use the common comma-separated parameter syntax, covering more binary string operation scenarios. This section describes the binary string functions and operators provided by DWS, helping users correctly select and use these functions in actual services.

Concepts

  • bytea type: It is a binary data type in DWS and is used to store raw byte data. The data of the bytea type is displayed in hexadecimal format starting with \x. For example, \x546f6d indicates the byte sequence of the string "Tom". In SQL statements, you can use the E'\\xxx'::bytea syntax to define a bytea literal, where \\xxx is the octal escape representation.
  • SQL keyword syntax: Some binary string functions defined in the SQL standard use keywords instead of commas to separate parameters. For example, substring(string from int for int) uses the from and for keywords to specify the start position and length, and position(substring in string) uses the in keyword to specify the search range. These functions are part of the SQL standard and have good compatibility.
  • Function call syntax: The extended binary string functions of DWS use the common comma-separated parameter syntax. For example, get_bit(string, offset) and btrim(string, bytes) are called in the same way as common functions.
  • Differences between binary strings and common strings: Common strings (text/varchar) are processed by character and are affected by the character set encoding. Binary strings (bytea) are processed by byte and are not affected by the character set encoding. They are suitable for storing and operating any binary data.

Classification of Binary String Functions

The binary string functions of DWS can be classified into the following five types:

Table 1 Function classification

Category

Function

Description

Length Query Functions

octet_length(string)

Queries the number of bytes in a binary string.

Search and Substring Functions

position(substring in string), substring(string [from int] [for int]), substring_index(string, delim, count)

Searches for the position of a substring and extracts the substring.

Replacement Functions

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

Replaces a substring in a binary string.

Trimming Functions

trim([both] bytes from string), btrim(string bytea, bytes bytea)

Deletes specified bytes from the beginning and end.

Bitwise and Byte Manipulation Functions

get_bit(string, offset), get_byte(string, offset), set_bit(string, offset, newvalue), set_byte(string, offset, newvalue)

Reads and modifies data bit by bit or byte by byte.

Length Query Functions

octet_length(string)

Description: Returns the number of bytes in the given binary string.

Return type: integer

Example:

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

Search and Substring Functions

position(substring in string)

Description: Returns the position (starting from 1) of the start of a specific substring in a binary string. If the substring is not found, 0 is returned.

Return type: integer.

Example:

1
2
3
4
5
6
-- Return the start position of the substring \000om in Th\000omas (counting from 1), and the result is 3, indicating that \000om starts from the 3rd byte (T= the 1st byte, h= the 2nd byte, \000= the 3rd byte).
SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT;
 result
--------
      3
(1 row)

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

Description: Extracts a substring from a binary string. from specifies the start position (counting from 1), and for specifies the length of the substring. If from is omitted, the substring starts from the first byte. If for is omitted, the substring is truncated to the end of the string.

Return type: bytea.

Example:

1
2
3
4
5
6
-- The 2nd to 4th bytes are extracted, that is, 68 00 6F. The result is \x68006f, which is the hexadecimal representation of the 3 bytes (68='h', 00= empty byte, 6f='o')
SELECT substring(E'Th\\000omas'::bytea from 2 for 3) AS RESULT; 
  result  
----------
 \x68006f
(1 row)

Obtain the hour number from the time stamp:

1
2
3
4
5
SELECT substring('2022-07-18 14:38:15',12,2)AS RESULT;
 result
-----------
 14
(1 row)

substring_index(string, delim, count)

Description: Searches for the delimiter based on case-sensitive matching and returns the substring before the countth occurrence of the delimiter in the string. If count is a negative number, the function searches for the delimiter from the end to the beginning. If any parameter is NULL, NULL is returned. This function is supported only in clusters of version 8.2.0 or later.

Return type: text.

Example: Searches for the delimiter .wWw. based on case-sensitive matching and returns the substring www.wWw.cloud before the second occurrence of the delimiter in the string www.wWw.cloud.wWw.com.

1
2
3
4
5
SELECT SUBSTRING_INDEX('www.wWw.cloud.wWw.com', '.wWw.', 2) AS RESULT;
    result
---------------
 www.wWw.cloud
(1 row)

Replacement Functions

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

Description: Replaces a substring in a binary string. It replaces the substring starting from the position specified by from in string with the string specified by placing. The length of the substring to be replaced is specified by for. If for is omitted, the length of the substring to be replaced is equal to the length of the string specified by placing.

Return type: bytea

Example:

1
2
3
4
5
6
-- Starting from the 2nd byte, replace the three bytes (68 00 6F) in the original string with the replacement string (02 03). The result is \x5402036d6173. The 2nd to 4th bytes (68 00 6F) in the original string are replaced with (02 03), resulting in 54 02 03 6D 61 73.
SELECT overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) AS RESULT;
     result     
----------------
 \x5402036d6173
(1 row)

Trimming Functions

trim([both] bytes from string)

Description: Removes the longest string containing only bytes from bytes from the start and end of string. The keyword both can be omitted, indicating that both the beginning and end are trimmed.

Return type: bytea.

Example:

1
2
3
4
5
6
-- Delete the \000 null bytes at the beginning and end of the original string. The output is \x546f6d, that is, "Tom" (54='T', 6f='o', 6d='m'). The null bytes at the beginning and end of the string have been deleted.
SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) AS RESULT;
  result  
----------
 \x546f6d
(1 row)

btrim(string bytea,bytes bytea)

Description: Removes the longest string containing only bytes from bytes from the start and end of string. It has the same functionality as trim([both] bytes from string), but uses the function call syntax with comma-separated parameters.

Return type: bytea.

Example:

1
2
3
4
5
6
-- Delete the \000 null bytes at the beginning and end of the original string. The function is the same as that of trim, but the parameter syntax is separated by commas. The output is \x7472696d, that is, "trim" (74='t', 72='r', 69='i', 6d='m'). The \000 null bytes at the beginning and end have been deleted.
SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) AS RESULT;
   result   
------------
 \x7472696d
(1 row)

Bitwise and Byte Manipulation Functions

The following functions support exact bit-by-bit or byte-by-byte reading and modification of binary strings. The offset parameter is counted from 0.

get_bit(string, offset)

Description: Extracts a bit from a specified position in a string. The return value is 0 or 1.

Return type: integer

Example:

1
2
3
4
5
6
-- Obtain the value (0 or 1) of the 45th bit. The output is 1, indicating that the value of the 45th bit is 1 (the 5th byte is 0x61=01100001, and the 5th bit is 1).
SELECT get_bit(E'Th\\000omas'::bytea, 45) AS RESULT; 
 result
--------
      1
(1 row)

get_byte(string, offset)

Description: Extracts a byte from a specified position in a string. The return value is the ASCII code of the byte (0–255).

Return type: integer.

Example:

1
2
3
4
5
6
7
8
-- Input: E'Th\\000omas' (byte sequence: 54 68 00 6F 6D 61 73), offset=4 (the 5th byte, counting from 0)
-- Function: Obtain the byte value at the position where offset=4.
SELECT get_byte(E'Th\\000omas'::bytea, 4) AS RESULT; 
 result
--------
    109
(1 row)
-- Output: 109, which is the 'm' ASCII code value (0x6D = decimal 109)

set_bit(string,offset, newvalue)

Description: Sets the bit value at the specified position in a string. The value of newvalue is 0 or 1. The modified string is returned, and the original string remains unchanged.

Return type: bytea.

Example:

1
2
3
4
5
6
7
8
-- Input: E'Th\\000omas' (54 68 00 6F 6D 61 73), offset=45 (the 5th bit of the 5th byte), newvalue=0
-- Function: Set the value of the 45th bit from 1 to 0.
SELECT set_bit(E'Th\\000omas'::bytea, 45, 0) AS RESULT; 
      result      
------------------
 \x5468006f6d4173
(1 row)
-- Output: \x5468006f6d4173. The 5th byte changes from 0x61('a') to 0x41('A'), that is, the 5th bit changes from 1 to 0.

set_byte(string,offset, newvalue)

Description: Sets the byte value at the specified position in a string. The value of newvalue is an integer ranging from 0 to 255. The modified string is returned, and the original string remains unchanged.

Return type: bytea.

Example:

1
2
3
4
5
6
7
8
-- Input: E'Th\\000omas' (54 68 00 6F 6D 61 73), offset=4 (the 5th byte), newvalue=64
-- Function: Change the value of the 5th byte from 0x6D (109) to 64 (0x40='@')
SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; 
      result      
------------------
 \x5468006f406173
(1 row)
-- Output: \x5468006f406173. The 5th byte is changed from 6D to 40, that is, 'm' is changed to '@'