Regular expression functions (regexp_match, regexp_replace) in PostgreSQL - Mini Project: Build & Apply

Using Regular Expression Functions in PostgreSQL

📖 Scenario: You are managing a customer database for an online store. Some customer phone numbers are stored in different formats. You want to clean and extract parts of these phone numbers using regular expressions.

🎯 Goal: Build SQL queries using PostgreSQL's regexp_match and regexp_replace functions to extract area codes and standardize phone number formats.

📋 What You'll Learn

Create a table called customers with a phone column containing specific phone numbers.

Add a variable or setting to define the pattern for area codes.

Write a query using regexp_match to extract the area code from each phone number.

Write a query using regexp_replace to format all phone numbers into the standard format (XXX-XXX-XXXX).

💡 Why This Matters

🌍 Real World

Cleaning and standardizing phone numbers is common in customer databases to ensure consistent data for communication.

💼 Career

Database administrators and data analysts often use regular expressions in SQL to extract and transform text data efficiently.

1

Create the customers table with phone numbers

Create a table called customers with a column phone of type text. Insert these exact phone numbers as rows: '[phone]', '123.456.7890', '123-456-7890', '1234567890'.

PostgreSQL

-- Create the customers table and insert phone numbers
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the phone numbers exactly as shown.

2

Define the area code pattern

Create a variable called area_code_pattern and set it to the regular expression string '\\d{3}' which matches exactly three digits.

PostgreSQL

CREATE TABLE customers (phone text);
INSERT INTO customers (phone) VALUES
  ('[phone]'),
  ('123.456.7890'),
  ('123-456-7890'),
  ('1234567890');

-- Define the area_code_pattern variable
-- Your code here

Need a hint?

Use the \set command in psql to create a variable with the pattern string.

3

Extract area codes using regexp_match

Write a SQL query selecting phone and the first match of the area code using regexp_match(phone, '\\d{3}') as area_code from the customers table.

PostgreSQL

CREATE TABLE customers (phone text);
INSERT INTO customers (phone) VALUES
  ('[phone]'),
  ('123.456.7890'),
  ('123-456-7890'),
  ('1234567890');

\set area_code_pattern '\\d{3}'

-- Write a query to select phone and extract area_code using regexp_match
-- Your code here

Need a hint?

Use regexp_match to get the first group matching three digits and select it as area_code.

4

Standardize phone numbers using regexp_replace

Write a SQL query selecting phone and a new column standard_phone where you use regexp_replace to convert all phone numbers into the format XXX-XXX-XXXX. Use the pattern '\\D' to remove all non-digit characters first, then insert dashes with regexp_replace.

PostgreSQL

CREATE TABLE customers (phone text);
INSERT INTO customers (phone) VALUES
  ('[phone]'),
  ('123.456.7890'),
  ('123-456-7890'),
  ('1234567890');

\set area_code_pattern '\\d{3}'

SELECT phone, (regexp_match(phone, '\\d{3}'))[1] AS area_code FROM customers;

-- Write a query to select phone and standard_phone formatted as XXX-XXX-XXXX
-- Your code here

Need a hint?

First remove all non-digit characters with regexp_replace(phone, '\\D', '', 'g'). Then apply another regexp_replace to insert dashes between groups of digits.