SQL › Working with Text and Numbers

String and Math Functions

Functions take a value and return a new one: a name in capitals, the first word of a sentence, a price rounded to the nearest hundred. They work on each row, in SELECT, WHERE or ORDER BY.

Syntax

Order of execution: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT

A function in SELECT runs once per row that WHERE kept. Its alias can be used in ORDER BY, but not in WHERE, which runs earlier.

Code
SELECT function_name(column, argument, ...) AS alias
FROM table_name;

Examples

Changing case and length

Make text look the same everywhere, or measure it.

Code
SELECT UPPER(city), LOWER(city), INITCAP('new delhi') FROM customers;
SELECT product_name, LENGTH(product_name) AS name_length FROM products;

Cutting text

Take part of a value: the start, the end, or a piece between separators.

Code
SELECT LEFT(product_name, 5), RIGHT(product_name, 3) FROM products;
SELECT SUBSTRING(account_no FROM 4 FOR 3) FROM accounts;             -- 3 characters from the 4th
SELECT full_name, SPLIT_PART(full_name, ' ', 2) AS last_name FROM customers;

Replacing and trimming

Clean values before you show or compare them.

Code
SELECT REPLACE(product_name, ' - ', ': ') FROM products;
SELECT TRIM('  Yoga Mat  ');                                         -- 'Yoga Mat'

Joining text

Build one value out of several, and watch out for NULLs.

Code
-- || gives NULL if any part is NULL
SELECT product_name || ' (' || rating || ' stars)' FROM products;
 
-- CONCAT skips NULLs
SELECT CONCAT(product_name, ' (', rating, ' stars)') FROM products;

Rounding

Round a number to show it, or round in one direction on purpose.

Code
SELECT price * 0.85 AS sale,
       ROUND(price * 0.85, 2) AS to_paise,
       ROUND(price * 0.85, -2) AS to_hundred,
       TRUNC(price * 0.85) AS cut_off,
       CEIL(price * 0.85) AS up,
       FLOOR(price * 0.85) AS down
FROM products;

Integer division

A whole number divided by a whole number stays a whole number.

Code
SELECT 7 / 2, 7 % 2, 7 / 2.0;              -- 3, 1, 3.5
SELECT stock, stock / 10 AS full_tens, stock % 10 AS left_over FROM products;

In other databases

Function names differ more than any other part of SQL. UPPER, LOWER, REPLACE, TRIM, ROUND and FLOOR work everywhere; for the rest:

Remember

• In PostgreSQL, || returns NULL when any part is NULL and CONCAT treats NULL as empty text.
• In PostgreSQL and SQL Server, integer / integer drops the remainder: 7 / 2 is 3. Make one side a decimal (2.0) to keep it.
• Positions start at 1, not 0.
• ROUND(x, -2) rounds to hundreds; CEIL always goes up and FLOOR always goes down.

Practice

1. Display Names From Emails (Easy)
2. Packing Into Boxes of 12 (Easy)
3. Masked Phone Numbers (Easy)