String Manipulation Functions: Essential SQL Text Formatting

Previously in this course, we finished our store audit in Performing a Database Audit: Reviewing the Store Application. Now that our schema is solid, we often need to refine how that data appears to the end user. This lesson introduces string functions, which allow you to manipulate text on the fly during your SELECT operations.

While raw data is perfect for storage, reports and user interfaces often require specific formatting—such as combining names or truncating long descriptions.

Why String Functions Matter in SQL Text Formatting

In a well-designed database, you store data in its most granular, atomic form (e.g., first_name and last_name in separate columns). However, when querying this data, you rarely want to present it exactly as it sits on disk.

Using string functions allows you to perform "presentation-layer" logic directly inside the database. This is faster and more consistent than pulling raw data into your application code just to perform basic formatting.

1. Combining Data with CONCAT

The CONCAT function joins two or more strings together. Unlike the || operator, CONCAT automatically ignores NULL values, which is safer when dealing with incomplete customer records.

SQL
-- Combining first and last name for a full name display
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

2. Standardizing Case with UPPER and LOWER

Data entry is often inconsistent. One user might type "apple" and another "APPLE". To normalize these for reports or display, use UPPER() or LOWER().

SQL
-- Convert product names to uppercase for a catalog header
SELECT UPPER(product_name) AS display_name
FROM products;

3. Extracting Text with SUBSTRING

Sometimes you only need a portion of a string. SUBSTRING(string FROM start FOR length) allows you to slice text. For example, if you want to generate a short "SKU code" prefix based on the first three letters of a product category:

SQL
-- Extract the first 3 characters of a category
SELECT SUBSTRING(category_name FROM 1 FOR 3) AS category_code
FROM products;

Worked Example: Refining the Customer Report

Let’s apply these to our store application. Imagine we want to generate a clean "Contact Sheet" for our admin dashboard. We need to display the user's full name in uppercase and show a truncated version of their email username.

SQL
SELECT 
    CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS full_name,
    SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS email_username
FROM customers;

Note: POSITION('@' IN email) finds the index of the '@' symbol, allowing us to dynamically extract everything before the domain regardless of the email length.

Hands-on Exercise

Using our products table, write a query that:

1. Concatenates the product_name with the price column (e.g., "Widget - $10.00").
2. Returns the first 10 characters of the description column in lowercase.
3. Use an alias for each calculated column to keep the output readable.

Common Pitfalls

• Forgetting Aliases: Always use AS to rename calculated columns. Without it, your output header will be the entire string function (e.g., concat(first_name, ...)), which is hard to read.
• Off-by-one Errors with SUBSTRING: Remember that SUBSTRING in PostgreSQL is 1-indexed (the first character is position 1, not 0).
• Performance: While these functions are efficient, avoid using them in a WHERE clause filter on large tables if possible (e.g., WHERE LOWER(name) = 'widget'). This prevents PostgreSQL from using standard indexes. We will look at how to optimize these searches in MySQL Full-Text Search vs PostgreSQL tsvector: Which to Choose?.

FAQ

Q: Can I use CONCAT with numbers? A: Yes. PostgreSQL will implicitly cast numbers to strings when using CONCAT.

Q: What happens if I use SUBSTRING with a length longer than the string? A: PostgreSQL simply returns the entire string without throwing an error.

Q: Is UPPER() enough for case-insensitive comparisons? A: It works for simple tasks, but for production search, you should look into ILIKE or functional indexes, which we will cover in the next few lessons.

Recap

You’ve learned the building blocks of text manipulation. By using CONCAT for joining, UPPER/LOWER for standardization, and SUBSTRING for extraction, you can transform raw database rows into polished information. These tools are essential for any data-driven application.

Up next: We will dive into pattern matching and the LIKE operator to search for specific text sequences within your database.