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

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

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

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

Hint

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

Practice

1. What is the main advantage of using PostgreSQL string functions when working with text data?

easy

A. They automatically create indexes on text columns.

B. They encrypt text data for security.

C. They convert text data into numeric data types.

D. They simplify text manipulation tasks like searching and modifying strings.

Solution

1. Step 1: Understand the purpose of string functions

   PostgreSQL string functions are designed to help manipulate and analyze text data easily.

2. Step 2: Identify the correct advantage

   Among the options, only simplifying text manipulation tasks like searching and modifying strings matches the purpose of string functions.

3. Final Answer:

   They simplify text manipulation tasks like searching and modifying strings. -> Option D

4. Quick Check:

   String functions simplify text tasks = B [OK]

Hint: String functions help change and find text easily [OK]

Common Mistakes:

• Confusing string functions with indexing features
• Thinking string functions convert data types
• Assuming string functions handle encryption

2. Which of the following is the correct syntax to get the length of a string in PostgreSQL?

easy

A. LEN('hello world')

B. STRLEN('hello world')

C. LENGTH('hello world')

D. SIZE('hello world')

Solution

1. Step 1: Recall PostgreSQL string length function

   The correct function to get string length in PostgreSQL is LENGTH().

2. Step 2: Compare options

   Only LENGTH('hello world') matches the correct syntax; others are invalid in PostgreSQL.

3. Final Answer:

   LENGTH('hello world') -> Option C

4. Quick Check:

   LENGTH() gets string length = A [OK]

Hint: Use LENGTH() to find string size in PostgreSQL [OK]

Common Mistakes:

• Using LEN() which is from other SQL dialects
• Using STRLEN() or SIZE() which don't exist in PostgreSQL
• Forgetting to use parentheses

3. What will be the output of this query?

SELECT UPPER(SUBSTRING('PostgreSQL', 1, 4));

medium

A. 'post'

B. 'POST'

C. 'POSTG'

D. 'Post'

Solution

1. Step 1: Understand SUBSTRING function

   SUBSTRING('PostgreSQL', 1, 4) extracts the first 4 characters: 'Post'.

2. Step 2: Apply UPPER function

   UPPER('Post') converts it to uppercase: 'POST'.

3. Final Answer:

   'POST' -> Option B

4. Quick Check:

   UPPER(SUBSTRING()) = 'POST' [OK]

Hint: SUBSTRING extracts; UPPER converts to uppercase [OK]

Common Mistakes:

• Confusing substring length or start position
• Ignoring case conversion by UPPER()
• Assuming SUBSTRING returns more characters

4. Identify the error in this PostgreSQL query:

SELECT CONCAT('Hello', 123);

medium

A. The query is correct and will run without error.

B. Missing space between arguments in CONCAT.

C. CONCAT cannot combine strings and numbers directly.

D. CONCAT requires at least three arguments.

Solution

1. Step 1: Check CONCAT function behavior

   CONCAT in PostgreSQL automatically converts non-string types to text.

2. Step 2: Analyze the query

   CONCAT('Hello', 123) concatenates 'Hello' and '123' as 'Hello123' without error.

3. Final Answer:

   The query is correct and will run without error. -> Option A

4. Quick Check:

   CONCAT auto-converts types = C [OK]

Hint: CONCAT auto-converts numbers to text [OK]

Common Mistakes:

• Assuming CONCAT needs only strings
• Thinking CONCAT requires spaces between arguments
• Believing CONCAT needs 3+ arguments

5. You want to extract the domain name from email addresses stored in a column email. Which PostgreSQL string function combination is best to get the part after '@'?

hard

A. SUBSTRING(email FROM POSITION('@' IN email) + 1)

B. LEFT(email, POSITION('@' IN email))

C. RIGHT(email, LENGTH(email) - POSITION('@' IN email) - 1)

D. TRIM(email, '@')

Solution

1. Step 1: Understand the goal

   We want the text after '@' in the email string.

2. Step 2: Analyze each option

   SUBSTRING(email FROM POSITION('@' IN email) + 1) uses SUBSTRING starting just after '@', correctly extracting the domain.
   LEFT(email, POSITION('@' IN email)) extracts left part including '@', not domain.
   RIGHT(email, LENGTH(email) - POSITION('@' IN email) - 1) correctly calculates length for RIGHT to get domain.
   TRIM(email, '@') trims '@' characters from ends, not extracting domain.

3. Final Answer:

   SUBSTRING(email FROM POSITION('@' IN email) + 1) -> Option A

4. Quick Check:

   SUBSTRING after '@' = domain [OK]

Hint: Use SUBSTRING from position after '@' to get domain [OK]

Common Mistakes:

• Using LEFT which gets text before '@'
• Miscalculating length for RIGHT function
• Using TRIM which removes characters only at ends