TRIM, LTRIM, RTRIM variations in PostgreSQL - Mini Project: Build & Apply

TRIM, LTRIM, RTRIM variations

📖 Scenario: You work in a company database where customer names sometimes have extra spaces at the start or end. These spaces cause problems when searching or sorting names.

🎯 Goal: You will create a table with customer names that include extra spaces. Then, you will write queries using TRIM, LTRIM, and RTRIM functions to clean these names by removing unwanted spaces.

📋 What You'll Learn

Create a table named customers with a column name of type VARCHAR.

Insert exactly these names with spaces: ' Alice ', ' Bob', 'Charlie ', ' Diana '.

Write a query selecting all names trimmed on both sides using TRIM.

Write a query selecting all names trimmed only on the left side using LTRIM.

Write a query selecting all names trimmed only on the right side using RTRIM.

💡 Why This Matters

🌍 Real World

Cleaning up text data in databases is common to ensure consistent searching, sorting, and reporting.

💼 Career

Database administrators and developers often use trimming functions to prepare and clean data for applications.

1

Create the customers table and insert names with spaces

Create a table called customers with a column name of type VARCHAR(20). Then insert these exact names with spaces: ' Alice ', ' Bob', 'Charlie ', and ' Diana '.

PostgreSQL

-- Create the customers table and insert names with spaces
-- Your code here

Need a hint?

Use CREATE TABLE customers (name VARCHAR(20)); to create the table. Use INSERT INTO customers (name) VALUES (...); to add the names.

2

Add a query to select names trimmed on both sides using TRIM

Write a query selecting the column name trimmed on both sides using the TRIM function. Name the trimmed column trimmed_name. Use SELECT TRIM(name) AS trimmed_name FROM customers;.

PostgreSQL

CREATE TABLE customers (name VARCHAR(20));

INSERT INTO customers (name) VALUES
('  Alice  '),
('  Bob'),
('Charlie  '),
('  Diana  ');

-- Write a query selecting trimmed names using TRIM
-- Your code here

Need a hint?

Use SELECT TRIM(name) AS trimmed_name FROM customers; to remove spaces from both sides.

3

Add a query to select names trimmed only on the left side using LTRIM

Write a query selecting the column name trimmed only on the left side using the LTRIM function. Name the trimmed column left_trimmed_name. Use SELECT LTRIM(name) AS left_trimmed_name FROM customers;.

PostgreSQL

CREATE TABLE customers (name VARCHAR(20));

INSERT INTO customers (name) VALUES
('  Alice  '),
('  Bob'),
('Charlie  '),
('  Diana  ');

SELECT TRIM(name) AS trimmed_name FROM customers;

-- Write a query selecting names trimmed on the left side using LTRIM
-- Your code here

Need a hint?

Use SELECT LTRIM(name) AS left_trimmed_name FROM customers; to remove spaces only from the left side.

4

Add a query to select names trimmed only on the right side using RTRIM

Write a query selecting the column name trimmed only on the right side using the RTRIM function. Name the trimmed column right_trimmed_name. Use SELECT RTRIM(name) AS right_trimmed_name FROM customers;.

PostgreSQL

CREATE TABLE customers (name VARCHAR(20));

INSERT INTO customers (name) VALUES
('  Alice  '),
('  Bob'),
('Charlie  '),
('  Diana  ');

SELECT TRIM(name) AS trimmed_name FROM customers;

SELECT LTRIM(name) AS left_trimmed_name FROM customers;

-- Write a query selecting names trimmed on the right side using RTRIM
-- Your code here

Need a hint?

Use SELECT RTRIM(name) AS right_trimmed_name FROM customers; to remove spaces only from the right side.