SQL CASE Expression and Functions

The SQL CASE expression adds if-then-else logic to a query. Combined with built-in functions it lets you clean, label and format data directly in SQL.

CASE WHEN

Label products by price band. CASE checks each WHEN in order and returns the first match; ELSE is used when nothing matches.

SELECT ProductName, Price,
  CASE
    WHEN Price < 10 THEN 'Budget'
    WHEN Price < 16 THEN 'Standard'
    ELSE 'Premium'
  END AS PriceBand
FROM Products
ORDER BY Price;

ProductName    | Price | PriceBand
---------------+-------+----------
Dark Chocolate | 4.25  | Budget
Green Tea      | 6     | Budget
Wheat Flour    | 8     | Budget
Honey          | 11.5  | Standard
Basmati Rice   | 12.5  | Standard
Almonds        | 15    | Standard
Olive Oil      | 18.75 | Premium
Coffee Beans   | 22    | Premium
(8 rows)

CASE Inside an Aggregate

CASE inside SUM or COUNT is a powerful trick to count several conditions in one pass.

SELECT
  SUM(CASE WHEN Stock = 0 THEN 1 ELSE 0 END) AS out_of_stock,
  SUM(CASE WHEN Stock BETWEEN 1 AND 100 THEN 1 ELSE 0 END) AS low_stock,
  SUM(CASE WHEN Stock > 100 THEN 1 ELSE 0 END) AS healthy
FROM Products;

out_of_stock | low_stock | healthy
-------------+-----------+--------
1            | 3         | 4
(1 row)

String Functions

UPPER, LOWER, LENGTH, SUBSTR, REPLACE and TRIM are available almost everywhere (SQL Server uses LEN and SUBSTRING).

SELECT UPPER(CustomerName) AS name_upper,
       LENGTH(City) AS city_len,
       SUBSTR(Country, 1, 3) AS short_country
FROM Customers
LIMIT 3;

name_upper     | city_len | short_country
---------------+----------+--------------
ALPHA TRADERS  | 6        | Pak
BLUE SKY FOODS | 7        | Pak
CEDAR MARKET   | 6        | UK
(3 rows)

Number and Date Functions

ROUND, ABS and arithmetic work the same in most databases. Date functions vary the most: SQLite uses date() and strftime(), MySQL uses DATE_ADD and DATEDIFF, SQL Server uses DATEADD and DATEDIFF.

SELECT OrderID, OrderDate,
       date(OrderDate, '+30 days') AS due_date,
       julianday('2026-06-01') - julianday(OrderDate) AS days_before_june
FROM Orders
WHERE OrderID IN (101, 110);

OrderID | OrderDate  | due_date   | days_before_june
--------+------------+------------+-----------------
101     | 2026-01-05 | 2026-02-04 | 147
110     | 2026-05-22 | 2026-06-21 | 10
(2 rows)

Frequently asked questions

Does SQL have an IF statement?

In queries, use CASE WHEN, which is standard SQL. MySQL also has IF(cond, a, b) and SQL Server has IIF(cond, a, b).

Can CASE be used in WHERE or ORDER BY?

Yes. CASE is an expression, so it can appear anywhere a value is allowed, including WHERE, ORDER BY and GROUP BY.

Why are SQL date functions different in every database?

The standard defines date types but vendors added their own functions before standardisation. Learn the concept, then look up the exact function names for your database.