Regular expression matching (~ operator) in PostgreSQL - Mini Project: Build & Apply

Regular Expression Matching with ~ Operator in PostgreSQL

📖 Scenario: You are managing a small library database. You want to find books whose titles contain certain patterns using PostgreSQL's regular expression matching.

🎯 Goal: Build a SQL query that uses the ~ operator to find books with titles matching a specific pattern.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert exactly these three rows into books: (1, 'The Great Gatsby'), (2, 'Great Expectations'), (3, 'Gone with the Wind').

Write a SQL query that selects all columns from books where the title matches the regular expression pattern for titles containing the word 'Great'.

Use the ~ operator for regular expression matching.

💡 Why This Matters

🌍 Real World

Regular expressions help find text patterns in database columns, useful for searching product names, user input, or logs.

💼 Career

Database developers and analysts often use regex matching to filter and analyze text data efficiently.

1

Create the books table

Write a SQL statement to create a table called books with two columns: id as an integer and title as text.

PostgreSQL

-- Create the books table with id and title columns
-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER, title TEXT); to create the table.

2

Insert data into books

Insert exactly these three rows into the books table: (1, 'The Great Gatsby'), (2, 'Great Expectations'), and (3, 'Gone with the Wind').

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT
);
-- Insert the three specified rows into books
-- Your code here

Need a hint?

Use a single INSERT INTO books (id, title) VALUES (...), (...), (...); statement.

3

Write a query using the ~ operator

Write a SQL SELECT statement to get all columns from books where the title matches the regular expression pattern for titles containing the word 'Great'. Use the ~ operator.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Great Expectations'),
(3, 'Gone with the Wind');

-- Select all columns where title matches 'Great' using ~ operator
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title ~ 'Great'; to find titles containing 'Great'.

4

Complete the query with case-insensitive matching

Modify the previous SQL query to use case-insensitive regular expression matching by using the ~* operator instead of ~.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Great Expectations'),
(3, 'Gone with the Wind');

-- Modify the query to use case-insensitive matching with ~* operator
-- Your code here

Need a hint?

Use ~* for case-insensitive regex matching in PostgreSQL.