On this page

octet_length(string)
overlay(string placing string from int [for int])
position(substring in string)
substring(string [from int] [for int])
trim([both] bytes from string)
btrim(string bytea,bytes bytea)
get_bit(string, offset)
get_byte(string, offset)
set_bit(string,offset, newvalue)
set_byte(string,offset, newvalue)

Show all

Help Center/ GaussDB(DWS)/ SQL Syntax Reference (8.1.3.x)/ Functions and Operators/ Binary String Functions and Operators

Binary String Functions and Operators

Updated on 2024-12-19 GMT+08:00

View PDF

There are some binary string functions defined in SQL, which use keywords instead of commas to separate arguments. GaussDB(DWS) also provides the common syntax used for invoking functions.

octet_length(string)

Description: Number of bytes in binary string

Return type: integer

Examples:

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

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

Description: Replaces substring.

Return type: bytea

Examples:

    
       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: Location of specified substring

Return type: integer

Examples:

    
       SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT;
 result
--------
      3
(1 row)

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

Description: Truncates substring.

Return type: bytea

Examples:

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

Truncate the time and obtain the number of hours.

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

trim([both] bytes from string)

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

Return type: bytea

Examples:

    
       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.

Return type: bytea

Examples:

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

get_bit(string, offset)

Description: Extracts bit from string.

Return type: integer

Examples:

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

get_byte(string, offset)

Description: Extracts byte from string.

Return type: integer

Examples:

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

set_bit(string,offset, newvalue)

Description: Sets bit in string.

Return type: bytea

Examples:

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

set_byte(string,offset, newvalue)

Description: Sets byte in string.

Return type: bytea

Examples:

    
       SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; 
      result      
------------------
 \x5468006f406173
(1 row)

Parent topic: Functions and Operators

Previous topic: Character Processing Functions and Operators

Next topic: Bit 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.

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