Introduction to PostgreSQL SUBSTRING() function

TheSUBSTRING() function allows you to extract a substring from a string and return the substring.

Here’s the basic syntax of theSUBSTRING() function:

SUBSTRING(string, start_position, length)

In this syntax:

• string: This is an input string with the data type char, varchar, text, and so on.
• start_position: This is an integer that specifies where in the string you want to extract the substring. Ifstart_position equals zero, the substring starts at the first character of the string. Thestart_position can be only positive. Note that in other database systems such as MySQL the SUBSTRING() function can accept a negativestart_position.
• length: This is a positive integer that determines the number of characters that you want to extract from the string beginning atstart_position. If the sum ofstart_position andlength is greater than the number of characters in thestring, the substring function returns the whole string beginning atstart_position. Thelength parameter is optional. If you omit it, theSUBSTRING function returns the whole string started atstart_position.

PostgreSQL offers another syntax for theSUBSTRING() function as follows:

SUBSTRING(stringFROM start_positionFOR length);

PostgreSQL provides another function namedSUBSTR() that has the same functionality as theSUBSTRING() function.

PostgreSQL SUBSTRING() function examples

Let’s explore some examples of using theSUBSTRING() function.

1) Basic SUBSTRING() function examples

The following example uses theSUBSTRING() function to extract the first 8 characters from the string PostgreSQL:

SELECT  SUBSTRING ('PostgreSQL',1,8);

Output:

substring----------- PostgreS(1 row)

In the example, we extract a substring that has a length of 8, starting at the first character of thePostgreSQL string. The result is etPostgreS as illustrated in the following picture:

The following example uses theSUBSTRING() function to extract the first 8 characters from the PostgreSQL string:

SELECT  SUBSTRING ('PostgreSQL',8);

Output:

substring----------- SQL(1 row)

In this example, we extract a substring started at position 8 and omit thelength parameter. The resulting substring starts at the position 8 to the rest of the string.

The following examples use the alternative syntax of theSUBSTRING() function:

SELECT  SUBSTRING ('PostgreSQL' FROM 1 FOR 8),  SUBSTRING ('PostgreSQL' FROM 8);

Output:

substring | substring-----------+----------- PostgreS  | SQL(1 row)

2) Using the PostgreSQL SUBSTRING() function with table data

We’ll use thecustomer table from thesample database:

The following example uses theSUBSTRING() function to retrieve the initial names of customers by extracting the first character of value in thefirst_name column:

SELECT  first_name,  SUBSTRING(first_name, 1, 1) AS initialFROM  customer;

Output:

first_name  | initial-------------+--------- Jared       | J Mary        | M Patricia    | P Linda       | L...

Extracting substring matching POSIX regular expression

In addition to the SQL-standard substring function, PostgreSQL allows you to extract a substring that matches a POSIX regular expression.

The following illustrates the syntax of the substring function with POSIX regular expression:

SUBSTRING(string, pattern);

Or you can use the following syntax:

SUBSTRING(stringFROM pattern)

If the SUBSTRING() function finds no match, it returns NULL.

If thepattern contains any parentheses, theSUBSTRING() function returns the text that matches the first parenthesized subexpression.

The following example uses theSUBSTRING() to extract the house number with 1 to 4 digits, from a string:

SELECT  SUBSTRING (    'The house number is 9001','([0-9]{1,4})'  )AS house_no

Output:

house_no---------- 9001(1 row)

Extracting substring matching a SQL regular expression

Besides the POSIX regular expression pattern, you can use the SQL regular expression pattern to extract a substring from a string using the following syntax:

SUBSTRING(string FROM pattern FOR escape-character)

In this syntax:

• string: is a string that you want to extract the substring.
• escape-character: the escape character.
• pattern is a regular expression wrapped inside escape characters followed by a double quote ("). For example, if the character# is the escape character, the pattern will be#"pattern#". In addition, thepattern must match the entirestring, otherwise, the function will fail and returnNULL.

For example:

SELECT  SUBSTRING ('PostgreSQL' FROM '%#"S_L#"%' FOR '#');

Output:

substring----------- SQL(1 row)

Summary

• Use the PostgreSQLSUBSTRING() functions to extract a substring from a string.

Last updated onJanuary 29, 2024

On this page

• Introduction to PostgreSQL SUBSTRING() function
• PostgreSQL SUBSTRING() function examples
• Extracting substring matching POSIX regular expression
• Extracting substring matching a SQL regular expression
• Summary