w3resource

Binary String Functions and Operators

Introduction

In this section, we have discussed functions and operators for examining and manipulating values of type bytea.

Binary String Operator

The PostgreSQL concatenate operator ( || ) is used to concatenate two or more binary strings.

Operator Syntax Example Output
| | ( concatenate operator )      

Binary String Functions

octet_length(string)

The octet_length() is used to get the number of bytes in a binary string.

Return Type : int

Example:

postgres=# SELECT octet_length(E'my\\000se'::bytea);
 octet_length
--------------
            5
(1 row)

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

The overlay() function is used to replace substring.

Return Type: bytea

Example:

postgres=# SELECT overlay(E'Th\\000omas'::bytea placing E'\\003\\004'::bytea 
from 3 for 4);
   overlay
--------------
 \x5468030473
(1 row)

position(substring in a string)

The position() function is used to get the position of specified substring.

Return Type: int

Example:

postgres=# SELECT position(E'\\000ok'::bytea in E'Th\\000okas'::bytea);
 position
----------
        3
(1 row)

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

The substring() function is used to extract a substring.

Return Type: bytea

Example:

postgres=# SELECT substring(E'Th\\000odam'::bytea from 2 for 3);
 substring
-----------
 \x68006f
(1 row)

trim([both] bytes from string)

The substring() function is used to remove the longest string containing only the bytes in bytes from the start and end of string

Return Type: bytea

Example:

postgres=# SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea);
  btrim
----------
 \x546f6d
(1 row))

Previous: Mathematical Operators
Next: Bit String Functions and Operators