Binary String Functions and Operators

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL : 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))