PostgreSQL

How to Use substring() in PostgreSQL

In PostgreSQL, the function substring() has many uses. The simplest one extracts a number of characters from the supplied string. Let's look at its syntax and parameters:

Syntax:

substring(original_string [from <starting_position>] [for <number_of_characters>])

Parameters:

  • original_string (required) - the string or column name to extract from
  • from starting_position (optional) - the position of the first character to be extracted. If not specified, the extraction will be from the first character.
  • for number_of_characters - the number of characters to be extracted. If not specified, the extraction will be up to the last character of the string.

Note that while the last two parameters are optional, you must use one of them. Here are some examples:

# select substring('Learning SQL is essential.' from 10);
     substring     
-------------------
 SQL is essential.
# select substring('Learning SQL is essential.' from 10 for 3);
 substring 
-----------
 SQL
# select substring('Learning SQL is essential.' for 13);
   substring   
---------------
 Learning SQL 

starting_position can also be a negative number. On its own it is ignored but when used with number_of_characters it causes the function to count “invisible” characters to the left of the string.

# select substring('Learning SQL is essential.', -4, 10);
 substring 
-----------
 Learn

There is also a version of this function that uses commas to separate the parameters instead of using the keywords from and for. In this case only the number of characters parameter is optional.

# select substring('Learning SQL is essential.', 10, 3);
 substring 
-----------
 SQL

# select substring('Learning SQL is essential.', 10);
     substring     
-------------------
 SQL is essential.