PostgreSQL – Substring Function

PostgreSQL is a powerful relational database management system with extensive text processing functions, including the flexible SUBSTRING function. This function enables users to extract specific portions of a string, making it essential for text manipulation, especially when dealing with large datasets or complex data structures.

In this article, we’ll explore the PostgreSQL SUBSTRING function in detail, including its syntax, key terms, and practical examples for real-world applications.

What is the PostgreSQL Substring Function?

The SUBSTRING function in PostgreSQL is designed to extract a defined part of a string. This function is highly useful for retrieving parts of text based on start and length parameters, or even based on regular expression patterns. The SUBSTRING function can be combined with other string functions, making it a flexible tool for data extraction and manipulation in PostgreSQL queries.

Syntax

SUBSTRING ( string, start_position, length )

Key Terms

• 'string': This is the source string from which the substring will be extracted. The data type can be char, varchar, text, etc.
• 'start_position': This integer value specifies the starting position from where the substring will be extracted.
• 'length': This integer value determines the number of characters to be extracted from the starting position.

Examples of PostgreSQL Substring Function

Let us take a look at some of the examples of the Substring Function in PostgreSQL to better understand the concept. In the following examples, we will demonstrate how to use the SUBSTRING function for various use cases, showcasing its flexibility and practical applications in PostgreSQL.

Example 1: Extracting Initials from Names

Here we query data from the customer table. Here we operate on the ‘last_name’ and ‘first_name’ column. We will extract the initial name by querying the first character of the ‘first_name’ column from the customer table of the sample database.

Query:

SELECT
    last_name,
    SUBSTRING( first_name, 1, 1 ) AS initial
FROM
    customer
ORDER BY
    last_name;

Output

Explanation:

This query selects the last name and the first character of the first name, renaming it as “initial.” Results are ordered by last_name.

Example 2: Extracting a Substring Using a Pattern

This example uses a regular expression to extract a specific pattern from a text string. Here, we’ll extract up to four consecutive digits to capture a house number. The following statement extracts the house number (maximum 4 digits, from 0 to 9) from a string:

Query:

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

Output

Explanation:

This query uses a regular expression to extract up to four consecutive digits from the input string. This query applies a regular expression pattern to extract up to four digits from the input string, returning the house number “9001.”

Important Points About PostgreSQL Substring Function

• Pattern Matching Support: The SUBSTRING function in PostgreSQL supports regular expressions, allowing for advanced pattern matching.
• Combining Functions: The SUBSTRING function can be combined with JSON functions to extract substrings from JSON data.
• Multi-byte Character Encodings: PostgreSQL’s SUBSTRING function supports multi-byte character encodings, such as UTF-8.
  
• Case-insensitive Extraction: Combine SUBSTRING with LOWER or UPPER functions to perform case-insensitive substring extraction.

Conclusion

The PostgreSQL SUBSTRING function is a highly flexible tool that enhances text processing capabilities by allowing targeted string extraction. Whether working with fixed positions or regular expression patterns, the SUBSTRING function helps PostgreSQL users manage and manipulate textual data with ease.

By mastering the SUBSTRING function and related text processing functions in PostgreSQL, developers can create efficient queries for handling textual data, making this function a must-know for effective data manipulation.