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

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

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

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

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.

Practice

1. What does the PostgreSQL function regexp_match('abc123', '\\d+') return?

easy

A. An error because of incorrect syntax

B. The string 'abc123' unchanged

C. An empty array because no digits are found

D. An array with the first sequence of digits found: ['123']

Solution

1. Step 1: Understand regexp_match function

   The function searches the input string for the first substring matching the pattern.

2. Step 2: Apply pattern '\\d+' to 'abc123'

   The pattern '\\d+' matches one or more digits. In 'abc123', '123' matches.

3. Final Answer:

   An array with the first sequence of digits found: ['123'] -> Option D

4. Quick Check:

   regexp_match returns first match array [OK]

Hint: regexp_match returns first matching group as array [OK]

Common Mistakes:

• Thinking regexp_match returns the whole string
• Expecting a string instead of an array
• Confusing regexp_match with regexp_replace

2. Which of the following is the correct syntax to replace all digits in 'abc123def' with '#' using regexp_replace in PostgreSQL?

easy

A. regexp_replace('abc123def', '\\d+', '#', 'g')

B. regexp_replace('abc123def', '[0-9]', '#', 'g')

C. regexp_replace('abc123def', '[0-9]+', '#')

D. regexp_replace('abc123def', '\\d', '#')

Solution

1. Step 1: Understand regexp_replace parameters

   regexp_replace(text, pattern, replacement, flags) replaces matching parts. To replace all matches, use flag 'g'.

2. Step 2: Choose pattern and flags to replace all digits

   Pattern '\\d+' matches one or more digits. Flag 'g' ensures all digit sequences replaced, not just first.

3. Final Answer:

   regexp_replace('abc123def', '\\d+', '#', 'g') -> Option A

4. Quick Check:

   Use 'g' flag to replace all matches [OK]

Hint: Use 'g' flag in regexp_replace to replace all matches [OK]

Common Mistakes:

• Omitting 'g' flag replaces only first match
• Using '\\d' without 'g' replaces only one digit
• Confusing pattern syntax with wrong brackets

3. What is the result of this query?

SELECT regexp_match('user_2023_data', 'user_(\\d{4})_data');

medium

A. NULL

B. ['user_2023_data']

C. ['2023']

D. An error due to wrong pattern

Solution

1. Step 1: Analyze the pattern 'user_(\\d{4})_data'

   The pattern looks for 'user_' followed by exactly 4 digits (captured), then '_data'.

2. Step 2: Match pattern against 'user_2023_data'

   The string matches perfectly, capturing '2023' as the group.

3. Final Answer:

   ['2023'] -> Option C

4. Quick Check:

   Captured group returns ['2023'] [OK]

Hint: Captured groups in regexp_match return as array [OK]

Common Mistakes:

• Expecting full string instead of captured group
• Not escaping backslashes properly
• Confusing regexp_match with regexp_replace output

4. Identify the error in this query:

SELECT regexp_replace('abc123def', '\\d+', '#');

It is intended to replace all digit sequences with '#'.

medium

A. regexp_replace cannot replace digits

B. Missing 'g' flag, so only first digit sequence replaced

C. Replacement '#' must be in double quotes

D. Pattern '\\d+' is invalid syntax

Solution

1. Step 1: Understand regexp_replace behavior without flags

   Without 'g' flag, regexp_replace replaces only the first match.

2. Step 2: Check the pattern and replacement

   Pattern '\\d+' is valid and replacement '#' is correct. Only missing 'g' flag causes partial replacement.

3. Final Answer:

   Missing 'g' flag, so only first digit sequence replaced -> Option B

4. Quick Check:

   Use 'g' flag to replace all matches [OK]

Hint: Add 'g' flag to replace all matches in regexp_replace [OK]

Common Mistakes:

• Assuming all matches replaced without 'g'
• Thinking pattern syntax is wrong
• Using single quotes incorrectly for replacement

5. You want to extract the domain name from email addresses like '[e-mail]' using regexp_match. Which query correctly extracts 'example'?

hard

A. SELECT (regexp_match('[e-mail]', '@([\\w]+)\\.com'))[1];

B. SELECT regexp_match('[e-mail]', '@([\\w]+)\\.com');

C. SELECT regexp_replace('[e-mail]', '.*@([\\w]+)\\.com', '\\1');

D. SELECT regexp_replace('[e-mail]', '@([\\w]+)\\.com', '\\1');

Solution

1. Step 1: Understand regexp_match returns array of groups

   regexp_match returns an array with captured groups. To get the domain, access first element [1].

2. Step 2: Check pattern '@([\\w]+)\\.com'

   This pattern captures word characters after '@' and before '.com', which is the domain.

3. Final Answer:

   SELECT (regexp_match('[e-mail]', '@([\\w]+)\\.com'))[1]; -> Option A

4. Quick Check:

   Access first group from regexp_match array [OK]

Hint: Use (regexp_match(...))[1] to get captured group string [OK]

Common Mistakes:

• Not accessing array element from regexp_match
• Using regexp_replace without global flag incorrectly
• Confusing pattern syntax for domain extraction