PostgreSQL Substring Function

Summary: in this tutorial, we will introduce you to PostgreSQL substring function that extracts a substring from a string.

Introduction to PostgreSQL substring function

The substring function returns a part of string. The following illustrates the syntax of the substring function:

SUBSTRING ( string ,start_position , length )
Code language: SQL (Structured Query Language) (sql)

Let’s examine each parameter in detail:

• string is a string whose data type is char, varchar, text, etc.
• start_position is an integer that specifies where you want to extract the substring. If start_position equals zero, the substring starts at the first character of the string. The start_position can be only positive. Though in other database systems such as MySQL the substring function can accept a negative start_position.
• length  is a positive integer that determines the number of characters that you want to extract from the string beginning at start_position. If the sum of start_position and length is greater than the number of characters in the string, the substring function returns the whole string beginning at start_position. The length parameter is optional. If you omit the length parameter, the substring function returns the whole string started at start_position.

PostgreSQL substring examples

See the following examples:

SELECT
	SUBSTRING ('PostgreSQL', 1, 8); -- PostgreS
SELECT
	SUBSTRING ('PostgreSQL', 8); -- SQL
Code language: SQL (Structured Query Language) (sql)

In the first statement, we extract a substring that has length of 8 and it is started at the first character of the PostgreSQL string. we get PostgreS as the result. See the following picture:

In the second statement, we extract a substring started at position 8 and we omit the length parameter. The substring is a string beginning at 8, which is SQL.

PostgreSQL provides another syntax of the substring function as follows:

substring(string from start_position for length);
Code language: SQL (Structured Query Language) (sql)

In this form, PostgreSQL puts three parameters into one. See the following example:

SELECT
	SUBSTRING ('PostgreSQL' FROM 1 FOR 8); -- PostgreS
SELECT
	SUBSTRING ('PostgreSQL' FROM 8); -- SQL
Code language: SQL (Structured Query Language) (sql)

The results are the same as the one in the first example.

In the following example, we query data from the customer table. We select last_name and first_name column. We get the initial name by extracting the first character of the first_name column.

SELECT
	last_name,
	SUBSTRING( first_name, 1, 1 ) AS initial
FROM
	customer
ORDER BY
	last_name;
Code language: SQL (Structured Query Language) (sql)

Extracting substring matching POSIX regular expression

In addition to the SQL-standard substring function, PostgreSQL allows you to use extract a substring that matches a POSIX regular expression. The following illustrates the syntax of the substring function with POSIX regular expression:

SUBSTRING(string FROM pattern)
Code language: SQL (Structured Query Language) (sql)

Or you can use the following syntax:

SUBSTRING(string,pattern);
Code language: SQL (Structured Query Language) (sql)

Note that if no match found, the substring function return a null value. If the pattern contains any parentheses, the substring function returns the text that matches the first parenthesized subexpression.

The following example extracts the house number (maximum 4 digits, from 0 to 9) from a string:

SELECT
	SUBSTRING (
		'The house no. is 9001',
		'([0-9]{1,4})'
	) as house_no
Code language: SQL (Structured Query Language) (sql)

Extracting substring matching a SQL regular expression

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

SUBSTRING(string FROM pattern FOR escape-character)
Code language: SQL (Structured Query Language) (sql)

This form of substring function accepts three parameters:

• 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, the pattern must match the entire string, otherwise, the substring function will fail and return a NULL value.

See the following examples:

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

SELECT SUBSTRING (
	'foobar'
	FROM
		'#"S_Q#"%' FOR '#'
); -- NULLL
Code language: SQL (Structured Query Language) (sql)

PostgreSQL provides another function named substr that has the same functionality as the substring function.

Summary

• Use the PostgreSQL substring functions to extract substring from a string.