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.

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