Transforming Data During Extraction

Modify column outputs on the fly using arithmetic and string manipulation expressions.

In this lesson, you will learn how to modify and format your data directly within your SELECT statement to make your query results more readable and useful.

Transforming Data During Extraction

Up until now, we have mostly retrieved data exactly as it is stored in the database. However, data is often stored in a "raw" format that might not be ready for human eyes. SQL allows us to perform arithmetic and string manipulation on the fly, meaning we can change how the data looks the moment it is pulled out of the table.

Arithmetic Expressions

If you have a column containing prices, you might want to see what those prices look like after a 10% tax or a discount. You can perform these calculations directly in the SELECT clause:

SELECT 
    product_name, 
    price, 
    price * 1.10 AS price_with_tax
FROM products;

Here, the database creates a brand-new column in the result set (price_with_tax) by calculating the values on the fly. This doesn't change the underlying data in the table; it only changes how it is presented in your current view.

String Manipulation

Sometimes, data is stored in a way that is difficult to read. For example, you might have a first_name and last_name column, but you want to see the full name combined. Using the pipe operator (||)—which is the standard way to concatenate strings in most SQL dialects—you can merge these columns:

SELECT 
    first_name || ' ' || last_name AS full_name
FROM users;

You can even combine these transformations. If you have a quantity column and a unit_price column, you can multiply them and add a custom label or combine text values to create a professional report without ever needing to update the database schema itself.

By performing these transformations during extraction, you keep your database storage clean and efficient while ensuring the output is perfectly tailored to your needs. You are essentially building a custom view of your data without the permanence of changing the source material.

Key Takeaway: SQL allows you to perform arithmetic and string operations inside the SELECT clause, enabling you to present calculated or formatted data without altering the original table content.