PostgreSQL Substring Function

Understanding PostgreSQL Substring Function

The PostgreSQL Substring Function is an essential tool for string manipulation in SQL queries. Whether you are a beginner or an intermediate PostgreSQL user, understanding how to extract parts of a string efficiently can significantly enhance your database operations and reporting.

What is the Substring Function in PostgreSQL?

The substring in PostgreSQL allows you to extract a specific portion of a string based on either position or pattern matching. It is widely used in data cleaning, formatting, and processing text stored in your database.

Basic Example of PostgreSQL Substring Function

Let’s start with a simple example:

📄
SELECT substring('PostgreSQLTutorial' from 1 for 10) AS result;

PostgreSQL allows extraction based on regular expressions, which is very powerful for dynamic data:

📄
SELECT substring('Order#12345' from '#([0-9]+)') AS order_number;

Output: 12345

Explanation: The pattern #([0-9]+) extracts numeric digits following the hash symbol (#).

Extracting Area Codes from Phone Numbers in PostgreSQL

PostgreSQL Substring Function for Area Code Extraction

Extracting area codes from phone numbers is a common task when working with customer data, contact lists, or communication logs. Using the PostgreSQL Substring Function, you can efficiently extract area codes from various phone number formats stored in your database.

Why Extract Area Codes?

• Analyze customer distribution by region
• Filter phone numbers by geographic location
• Prepare marketing or sales campaigns targeted by area
• Clean and standardize imported phone numbers

Phone Number Formats

Phone numbers can appear in multiple formats:

Using Substring to Extract Area Codes

Assume you have a table contacts with a column phone_number. Here’s how you can extract the area code for different formats:

1. Extract Area Code from Format (123) 456-7890

📄
SELECT 
  substring(phone_number from '\((\d{3})\)') AS area_code
FROM contacts;

Explanation: The regular expression \((\d{3})\) captures the three digits inside parentheses as the area code.

2. Extract Area Code from Format 123-456-7890

📄
SELECT 
  substring(phone_number from '^(\d{3})') AS area_code
FROM contacts;

Explanation: ^(\d{3}) captures the first three digits at the beginning of the string as the area code.

3. Extract Area Code from Format 1234567890

📄
SELECT 
  substring(phone_number from 1 for 3) AS area_code
FROM contacts;

Explanation: This uses the positional syntax to extract the first three characters directly.

Practical Example: Combining Multiple Formats

If your phone numbers have mixed formats, you can use a CASE statement:

📄
SELECT 
  phone_number,
  CASE
    WHEN phone_number ~ '^\(\d{3}\)' THEN substring(phone_number from '\((\d{3})\)')
    WHEN phone_number ~ '^\d{3}-' THEN substring(phone_number from '^(\d{3})')
    ELSE substring(phone_number from 1 for 3)
  END AS area_code
FROM contacts;

Explanation: This query handles different phone number formats and extracts the area code consistently.

Tips for Extracting Area Codes in PostgreSQL

• Use TRIM() to remove spaces before applying substring.
• Test your regular expressions with sample data to handle edge cases.
• Use ~ (regex match) for complex validation before extracting.
• Consider creating a generated column for area codes for faster queries.

Extracting area codes using the PostgreSQL Substring Function is straightforward with the right approach. By combining positional and pattern-based substring methods, you can handle multiple phone number formats efficiently, enabling better data analysis and reporting.

 Use Cases of Substring in PostgreSQL

• Extracting area codes from phone numbers stored as text.
• Separating first and last names from a full name column.
• Parsing identifiers or order numbers in e-commerce databases.
• Cleaning and formatting imported CSV data in PostgreSQL.

Practical PostgreSQL Substring Examples

Example 1: Extracting Domain from Email

📄
SELECT substring(email from '@(.+)$') AS domain
FROM users;

Explanation: This extracts the domain part of the email address using a regular expression.

Example 2: Extracting Year from Date String

📄
SELECT substring('2026-01-05' from 1 for 4) AS year;

Output: 2026 — Extracts the first four characters representing the year.

Example 3: Using Substring in a PostgreSQL SQL Query

📄
SELECT product_name, substring(product_code from 1 for 3) AS code_prefix
FROM products;

Explanation: This is helpful when you want to group or filter products by a code prefix.

Tips for Efficient String Manipulation in PostgreSQL

• Always check the string length before using substring() to avoid errors.
• Combine substring() with TRIM() to clean up unwanted spaces.
• Use pattern matching with regular expressions for flexible extraction.
• For large datasets, consider indexing frequently queried string patterns.

Comparison with Other PostgreSQL String Functions

The PostgreSQL Substring Function is a versatile tool for string extraction and manipulation. By mastering both the positional and pattern-based approaches, developers and analysts can efficiently process and clean textual data, build dynamic SQL queries, and improve database performance. Regular practice with examples and real-world use cases will help you become proficient in PostgreSQL string functions.

Frequently Asked Questions (FAQs)

1. What is the difference between substring() and LEFT() in PostgreSQL?

2. Can substring handle NULL values?

       Yes, if the input is NULL, substring() returns NULL.

3. How do I extract numbers from a text string?

   Use regex with substring. Example: substring('Order#12345' from '#([0-9]+)')  

4. Is substring case-sensitive?

 Yes, regex patterns in substring are case-sensitive unless you use case-insensitive flags.

5. Can substring be used in WHERE clauses?

      Yes, you can filter rows using substring.