SIMILAR TO for regex-lite matching in PostgreSQL - Mini Project: Build & Apply

Using SIMILAR TO for Simple Pattern Matching in PostgreSQL

📖 Scenario: You work at a bookstore database. You want to find books whose titles match simple patterns, like starting with certain letters or containing specific words.

🎯 Goal: Build a SQL query using SIMILAR TO to filter book titles by simple patterns.

📋 What You'll Learn

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

Insert exactly these three rows into books: (1, 'Learn SQL Basics'), (2, 'Advanced SQL Queries'), (3, 'SQL for Data Science').

Create a variable pattern with the value 'Learn%' to match titles starting with 'Learn'.

Write a SELECT query to get all columns from books where title SIMILAR TO pattern.

Add a final query to select titles matching a pattern that contains 'SQL' anywhere.

💡 Why This Matters

🌍 Real World

Filtering and searching text data in databases is common in many applications like bookstores, libraries, or content management systems.

💼 Career

Knowing how to use pattern matching in SQL helps database developers and analysts efficiently query and analyze text data.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Then insert these rows exactly: (1, 'Learn SQL Basics'), (2, 'Advanced SQL Queries'), and (3, 'SQL for Data Science').

PostgreSQL

-- Create table books and insert rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the pattern variable for matching titles starting with 'Learn'

Create a variable called pattern and set it to the string 'Learn%' to match titles starting with 'Learn'.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Learn SQL Basics'),
(2, 'Advanced SQL Queries'),
(3, 'SQL for Data Science');

-- Define pattern variable
-- Your code here

Need a hint?

Use \set pattern 'Learn%' in psql to define a variable.

3

Write a SELECT query using SIMILAR TO with the pattern variable

Write a SELECT query to get all columns from books where title SIMILAR TO :pattern using the variable pattern.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Learn SQL Basics'),
(2, 'Advanced SQL Queries'),
(3, 'SQL for Data Science');

\set pattern 'Learn%'

-- Select rows where title SIMILAR TO pattern
-- Your code here

Need a hint?

Use WHERE title SIMILAR TO :pattern to filter rows.

4

Add a query to select titles containing 'SQL' anywhere using SIMILAR TO

Write a SELECT query to get all columns from books where title SIMILAR TO '%SQL%' to find titles containing 'SQL' anywhere.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Learn SQL Basics'),
(2, 'Advanced SQL Queries'),
(3, 'SQL for Data Science');

\set pattern 'Learn%'

SELECT * FROM books WHERE title SIMILAR TO :pattern;

-- Select rows where title contains 'SQL' using SIMILAR TO
-- Your code here

Need a hint?

Use SIMILAR TO '%SQL%' to match titles containing 'SQL'.