Why PostgreSQL string functions are powerful - See It in Action

Why PostgreSQL string functions are powerful

📖 Scenario: You work in a small bookstore database. You have a table of book titles and authors. Sometimes the data is messy with extra spaces, mixed cases, or incomplete information. You want to clean and analyze this text data easily.

🎯 Goal: Build a simple PostgreSQL query that uses string functions to clean and manipulate book titles and author names. This will show how powerful and useful PostgreSQL string functions are for real-world text data.

📋 What You'll Learn

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

Insert 3 rows with messy book titles and author names exactly as specified.

Write a query that uses TRIM to remove extra spaces from title and author.

Use UPPER to convert the author names to uppercase.

Use SUBSTRING to extract the first 5 characters of the title.

💡 Why This Matters

🌍 Real World

Cleaning and formatting text data is common in databases for reports, searches, and user displays.

💼 Career

Database professionals often use string functions to prepare and analyze text data efficiently.

1

Create the books table and insert data

Create a table called books with columns id as integer, title as text, and author as text. Then insert these 3 rows exactly: (1, ' War and Peace ', 'Leo Tolstoy '), (2, 'Pride and Prejudice', ' Jane Austen'), (3, 'The Hobbit', 'J.R.R. Tolkien ').

PostgreSQL

-- Create the books table and insert the 3 rows below
-- Your code here

Need a hint?

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

2

Add a query to trim spaces from title and author

Write a SELECT query that returns id, title, and author from books. Use the TRIM function on both title and author to remove extra spaces.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, '  War and Peace  ', 'Leo Tolstoy  '),
  (2, 'Pride and Prejudice', ' Jane Austen'),
  (3, 'The Hobbit', 'J.R.R. Tolkien ');

-- Write a SELECT query using TRIM on title and author
-- Your code here

Need a hint?

Use TRIM(column_name) to remove spaces from the start and end of text.

3

Convert author names to uppercase

Modify the SELECT query to use UPPER on the trimmed author column to show author names in uppercase letters.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, '  War and Peace  ', 'Leo Tolstoy  '),
  (2, 'Pride and Prejudice', ' Jane Austen'),
  (3, 'The Hobbit', 'J.R.R. Tolkien ');

-- Modify the SELECT query to use UPPER on trimmed author
-- Your code here

Need a hint?

Use UPPER(text) to convert text to all uppercase letters.

4

Extract first 5 characters of title using SUBSTRING

Extend the SELECT query to include a new column called title_start that uses SUBSTRING to get the first 5 characters of the trimmed title.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, '  War and Peace  ', 'Leo Tolstoy  '),
  (2, 'Pride and Prejudice', ' Jane Austen'),
  (3, 'The Hobbit', 'J.R.R. Tolkien ');

-- Add a column title_start using SUBSTRING on trimmed title
-- Your code here

Need a hint?

Use SUBSTRING(text FROM start FOR length) to get part of a string.