SQL Basics Part 4: Numeric, Date, and String Transformations Plus CASE Conditionals

So far in this SQL series, I’ve covered creating tables and keys in Part 1, filtering and grouping in Part 2, and joining tables together in Part 3.

Part 4 focuses on transforming data inside a query: taking a column as it is stored in the table and reshaping it on the fly into something more useful. Whether you need a discounted price, a human-readable date, a cleaned-up string, or a custom category label built with CASE WHEN, transformations are where SQL stops feeling like static storage and starts feeling like a real data tool.

Numeric Transformations

Numeric columns don’t have to be returned exactly as they sit on disk. You can run arithmetic and rounding functions directly inside SELECT:

SELECT 
    unit_price * 0.90 AS discounted_price,
    unit_price + 10 AS taxed_price,
    unit_price / 10 AS fractioned_price,
    ROUND(unit_price, 1) AS rounded_price,
    unit_price * unit_price AS multiply_price
FROM dim_product;

Each expression takes the same unit_price column from dim_product and computes a derived column on the fly:

• unit_price * 0.90: Applies a 10% discount to the price.
• unit_price + 10: Adds a flat amount (like a fixed $10 tax or shipping fee).
• unit_price / 10: Divides the price by 10.
• ROUND(unit_price, 1): Rounds the value to 1 decimal place.
• unit_price * unit_price: Multiplies the column by itself.

Because these expressions live inside SELECT (rather than an UPDATE statement), none of this modifies the underlying dim_product table. The math only shapes the result set returned by this query.

Date Transformations in MySQL

Dates and timestamps are some of the most frequently transformed columns in SQL, and MySQL comes with a rich set of built-in date functions.

Getting the Current Date and Time (NOW vs UTC)

SELECT 
    date, 
    NOW() AS current_time_stamp,
    UTC_DATE(), 
    UTC_TIME(), 
    UTC_TIMESTAMP()
FROM dim_date;

• NOW() returns the current date and time according to the database server’s configured timezone.
• UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP() always return Coordinated Universal Time (UTC) regardless of where the server is hosted. Once you work with users or servers across multiple regions, storing and comparing timestamps in UTC saves you from timezone bugs.

Extracting Date Parts and Date Math

You can also break a date into individual components or calculate the gap between two dates:

SELECT 
    date, 
    YEAR(date) AS date_year,
    MONTH(date),
    WEEK(date),
    DAY(date),
    WEEKDAY(date),
    DAYNAME(date),
    DATEDIFF(DATE(UTC_TIMESTAMP), date) AS total_days,
    CAST('2026-10-5' AS DATETIME),
    TIME(DATE(UTC_TIMESTAMP)),
    DATE(UTC_TIMESTAMP),
    ADDDATE(date, -1)
FROM dim_date;

Here is what each function in that query is doing:

• YEAR(date), MONTH(date), WEEK(date), DAY(date): Extract just the year, month number (1 to 12), week number, or day of the month from date.
• WEEKDAY(date): Returns the day of the week as an integer where Monday is 0 and Sunday is 6.
• DAYNAME(date): Returns the English name of the weekday (such as 'Monday' or 'Friday') instead of a number.
• DATEDIFF(DATE(UTC_TIMESTAMP), date): Calculates the number of days between two dates (expr1 - expr2). Comparing today’s UTC date against date tells you how many days have elapsed since each row’s date.
• CAST('2026-10-5' AS DATETIME): Converts a plain string literal into a true DATETIME value (2026-10-05 00:00:00) so SQL can perform date math and comparisons on it.
• DATE(UTC_TIMESTAMP): Strips the time portion off a timestamp, leaving only YYYY-MM-DD.
• TIME(DATE(UTC_TIMESTAMP)): Because the inner DATE() function already stripped the time away, asking for the TIME() of a pure date returns '00:00:00'. It was a cool way to verify what DATE() actually outputs under the hood.
• ADDDATE(date, -1): Adds an interval to a date. Passing -1 subtracts one day (giving you the previous day), while passing a positive integer adds days.

Formatting Dates for Display with DATE_FORMAT

When you want to format a date for a report or UI instead of returning raw YYYY-MM-DD, MySQL provides DATE_FORMAT():

SELECT
    date,
    DATE_FORMAT(date, '%e %M %Y') AS day_formatted
FROM dim_date;

In the format string '%e %M %Y':

• %e is the day of the month without a leading zero (1 to 31).
• %M is the full month name (January through December).
• %Y is the 4-digit year.

So a raw date like 2026-10-05 comes back formatted as 5 October 2026.

Type Casting with CAST

Sometimes a column is stored as one data type (like an integer), but you need SQL to treat it as another type (like a string or a DATETIME):

SELECT 
    customer_key, 
    CAST(customer_key AS CHAR(100)) 
FROM dim_customer;

Here, customer_key is stored as a numeric ID, and CAST(customer_key AS CHAR(100)) converts it into a character string in the query output. Explicit casting is especially helpful when exporting formatted text, matching types across UNION queries, or working in stricter SQL engines that reject implicit number-to-string conversions.

String Functions

For cleaning up text columns, building display labels, or slicing strings apart, I practiced these ten string functions on dim_customer:

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT_WS(' ', first_name, last_name, country),
    LENGTH(country),
    LOWER(first_name),
    SUBSTRING(email, 1, 4),
    REPLACE(email, '@', ''),
    LEFT(country, 3),
    RIGHT(country, 4),
    REVERSE(country),
    REPEAT(first_name, 2)
FROM dim_customer;

Breaking down how each one works:

• CONCAT(first_name, ' ', last_name): Joins multiple strings together end-to-end. Note that in standard MySQL, if any argument passed to CONCAT() is NULL, the entire result becomes NULL.
• CONCAT_WS(' ', first_name, last_name, country): Stands for Concatenate With Separator. The first argument (' ') is placed between every value that follows. A huge bonus of CONCAT_WS() is that it gracefully skips NULL values instead of turning the whole string into NULL.
• LENGTH(country): Returns the length of the string in bytes (for standard ASCII characters, that matches the character count; for multi-byte UTF-8 text, CHAR_LENGTH() counts characters).
• LOWER(first_name): Converts all letters to lowercase (and UPPER() does the reverse).
• SUBSTRING(email, 1, 4): Extracts a slice of email starting at character 1 for 4 characters. One detail that tripped me up at first coming from programming languages like Python and C++: SQL string positions are 1-indexed, not 0-indexed, so the very first character is position 1.
• REPLACE(email, '@', ''): Replaces every occurrence of a substring with another string. Replacing '@' with an empty string '' strips the @ symbol out completely.
• LEFT(country, 3) and RIGHT(country, 4): Grab the first 3 characters from the start of country or the last 4 characters from the end.
• REVERSE(country): Reverses the character order of the string.
• REPEAT(first_name, 2): Repeats the string a specified number of times.

Conditional Logic with CASE WHEN

CASE WHEN is SQL’s if / else if / else expression. It lets you dynamically compute a new column’s value row-by-row based on conditions.

Simple CASE WHEN Bucketing

SELECT *,
    CASE
        WHEN unit_price <= 100 THEN 'affordable'
        WHEN unit_price <= 200 THEN 'normal' 
        ELSE 'expensive'
    END AS price_category
FROM dim_product;

SQL evaluates WHEN clauses from top to bottom and stops at the first condition that evaluates to true:

1. If a product has unit_price = 80, it matches WHEN unit_price <= 100, gets labeled 'affordable', and SQL immediately moves to the next row without checking unit_price <= 200.
2. If a product has unit_price = 150, the first WHEN is false, so it falls through to WHEN unit_price <= 200 and gets labeled 'normal'.
3. If no WHEN condition matches, it falls back to the ELSE value ('expensive'). (If you omit ELSE and nothing matches, SQL returns NULL.)

Combining Compound Conditions and Functions Inside CASE

You can also combine multiple conditions with AND / OR and call functions like CONCAT() right inside a THEN or ELSE branch:

SELECT *,
    CASE
        WHEN (unit_price <= 100 AND category = 'Clothing') THEN 'affordable'
        WHEN (unit_price <= 200 AND category = 'Clothing') THEN 'normal' 
        WHEN (unit_price > 200 AND category = 'Clothing') THEN 'expensive'
        ELSE CONCAT('not for you ', category)
    END AS price_category
FROM dim_product;

Here, a product only gets labeled 'affordable', 'normal', or 'expensive' if its category is 'Clothing'. Any row from another category fails all three WHEN checks and lands in ELSE, where CONCAT('not for you ', category) dynamically builds a label using that row’s actual category name (for example, 'not for you Footwear' or 'not for you Electronics').

Coming Up in Part 5

Next up in SQL Basics Part 5: Window Functions, Running Totals, Ranking, LAG/LEAD, and PARTITION BY, I cover OVER(), running totals with ROWS BETWEEN, ROW_NUMBER(), RANK(), DENSE_RANK(), PARTITION BY, and row-to-row comparisons with LAG() and LEAD().