How to Use substring() in PostgreSQL

The substring() function in PostgreSQL is used to extract a portion of a string based on a pattern or a specified length. This function is particularly useful when you need to manipulate string data within your database.

Syntax

substring(string FROM start FOR length)

The substring() function works in the following way:

• string: The string you want to extract the substring from.
• start: The position in the string where the extraction begins.
• length (optional): The number of characters to extract starting from the start position. If omitted, it extracts until the end of the string.

Examples

Basic Example

SELECT substring('PostgreSQL' FROM 1 FOR 4); -- Output: Post

This example extracts the first 4 characters of the string 'PostgreSQL' starting from position 1.

Extracting with Pattern Matching

SELECT substring('John Doe' FROM '^[a-zA-Z]+'); -- Output: John

In this example, we use a regular expression pattern to extract the first word in a string. The pattern '^[a-zA-Z]+' matches the first sequence of alphabetic characters.

Using substring() with a Table

SELECT substring(name FROM 1 FOR 3) AS short_name FROM users;

Here, the substring() function is applied to the name column in the users table to extract the first 3 letters of each user's name.

Practical Applications

• Data Cleaning: You can use substring() to remove unwanted characters or extract specific parts of a string (like domain names or phone numbers).
• Text Extraction: If you store structured data in a single column, you can use substring() to extract specific parts of that data for analysis.