Why string functions matter in queries in SQL - See It in Action

Why String Functions Matter in Queries

📖 Scenario: You work at a small bookstore that keeps track of book titles and authors in a database. Sometimes, the data is messy: extra spaces, inconsistent capitalization, or partial names. You want to clean up and search this data efficiently.

🎯 Goal: Build SQL queries that use string functions to clean and search text data in the bookstore database.

📋 What You'll Learn

Create a table called books with columns id, title, and author.

Insert sample data with inconsistent spacing and capitalization.

Use the TRIM() function to remove extra spaces from titles.

Use the UPPER() function to standardize author names.

Use the LIKE operator with string functions to find books by partial author name.

💡 Why This Matters

🌍 Real World

Cleaning and searching text data is common in databases for customer names, product descriptions, and more.

💼 Career

Knowing string functions helps database professionals write better queries for data cleaning, reporting, and search features.

1

Create the books table and insert sample data

Write SQL statements to create a table called books with columns id (integer), title (text), and author (text). Then insert these exact rows: (1, ' The Hobbit ', 'j.r.r. tolkien'), (2, '1984', 'george orwell'), (3, 'Brave New World', 'aldous huxley').

SQL

-- Create the books table and insert the sample data
-- 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 book titles

Write a SQL SELECT statement that returns the id and the trimmed title using the TRIM() function from the books table.

SQL

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

INSERT INTO books (id, title, author) VALUES
  (1, '  The Hobbit  ', 'j.r.r. tolkien'),
  (2, '1984', 'george orwell'),
  (3, 'Brave New World', 'aldous huxley');

-- Select id and trimmed title
-- Your code here

Need a hint?

Use TRIM(column_name) to remove spaces from both ends of the text.

3

Standardize author names to uppercase

Write a SQL SELECT statement that returns the id and the author names converted to uppercase using the UPPER() function from the books table.

SQL

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

INSERT INTO books (id, title, author) VALUES
  (1, '  The Hobbit  ', 'j.r.r. tolkien'),
  (2, '1984', 'george orwell'),
  (3, 'Brave New World', 'aldous huxley');

SELECT id, TRIM(title) AS trimmed_title FROM books;

-- Select id and uppercase author names
-- Your code here

Need a hint?

Use UPPER(column_name) to convert text to uppercase.

4

Find books by partial author name using string functions

Write a SQL SELECT statement that returns all columns from books where the uppercase author contains the substring 'ORWELL'. Use the UPPER() function and the LIKE operator with wildcards.

SQL

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

INSERT INTO books (id, title, author) VALUES
  (1, '  The Hobbit  ', 'j.r.r. tolkien'),
  (2, '1984', 'george orwell'),
  (3, 'Brave New World', 'aldous huxley');

SELECT id, TRIM(title) AS trimmed_title FROM books;

SELECT id, UPPER(author) AS uppercase_author FROM books;

-- Select all columns where uppercase author contains 'ORWELL'
-- Your code here

Need a hint?

Use LIKE '%substring%' to find text containing a substring.