Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Binary String Functions and Operators
Updated on 2025-09-18 GMT+08:00
View PDF
Share

Binary String Functions and Operators

SQL defines some binary string functions that use keywords rather than commas to separate arguments. Additionally, DataArts Fabric SQL provides commonly used syntax for invoking these functions.

octet_length(string)

Description: The number of bytes in a binary string.

Return type: integer.

Example:

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

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

Description: Replaces a substring.

Return type: bytea

Example:

1
2
3
4
5
 
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: The position of a specific substring.

Return type: integer.

Example:

1
2
3
4
5
 
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.

Return type: bytea

Example:

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

Extract time and get the hour value:

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

trim([both] bytes from string)

Description: Remove the longest string consisting only of bytes from the start and end of the string.

Return type: bytea

Example:

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

substring_index(string, delim, count)

Description: Performs a case-sensitive search for the delimiter and returns the substring preceding the count-th occurrence of the delimiter in the string. If count is negative, search backward from the end. If the parameter contains NULL, NULL is returned. This function is only supported by clusters of version 8.2.0 or later.

Return type: text.

Example: In the string www.wWw.cloud.wWw.com, find the delimiter .wWw. that appears the second time in a case-sensitive manner. Return the substring before it: www.wWw.cloud.

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

btrim(string bytea,bytes bytea)

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

Return type: bytea

Example:

1
2
3
4
5
 
SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) AS RESULT;
   result   
------------
 \x7472696d
(1 row)

get_bit(string, offset)

Description: Extracts bits from a string.

Return type: integer.

Example:

1
2
3
4
5
 
SELECT get_bit(E'Th\\000omas'::bytea, 45) AS RESULT; 
 result
--------
      1
(1 row)

get_byte(string, offset)

Description: Extracts bytes from a string.

Return type: integer.

Example:

1
2
3
4
5
 
SELECT get_byte(E'Th\\000omas'::bytea, 4) AS RESULT; 
 result
--------
    109
(1 row)

set_bit(string,offset, newvalue)

Description: Sets bits in a string.

Return type: bytea

Example:

1
2
3
4
5
 
SELECT set_bit(E'Th\\000omas'::bytea, 45, 0) AS RESULT; 
      result      
------------------
 \x5468006f6d4173
(1 row)

set_byte(string,offset, newvalue)

Description: Sets bytes in a string.

Return type: bytea

Example:

1
2
3
4
5
 
SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; 
      result      
------------------
 \x5468006f406173
(1 row)
Parent topic: Functions and Operators