String, Date and Number Functions
The everyday toolbox for reshaping the values inside your columns
Big Idea: A scalar function takes one or more values from a single row and returns one new value for that row. Unlike aggregates (
SUM, COUNT) that fold many rows into one, scalar functions run once per row and never change the number of rows. Mastering the string, date and number families lets you clean, format and compute directly in SQL instead of dragging raw data into application code.
By the end of this topic you will be able to:
- slice and reshape text with
SUBSTRING,TRIM,REPLACE,UPPER/LOWERandCONCAT; - do arithmetic on dates with
DATE_ADD,DATEDIFFand format them withDATE_FORMAT; - control precision with
ROUND,FLOOR,CEILandMOD; - convert between types deliberately using
CAST; - recognise where MySQL, PostgreSQL and SQLite disagree.
1. Our example schema
Every query below runs against one small shop database: a customers table (lots of text and a date) and an orders table (dates and money). Create it once and keep it in mind.
CREATE TABLE customers (
id INT PRIMARY KEY,
full_name VARCHAR(60),
email VARCHAR(80),
city VARCHAR(40),
signup_date DATE
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(9,3),
placed_at DATETIME
);
INSERT INTO customers VALUES
(1, ' Ada Lovelace', 'ADA@Example.com', 'London', '2023-11-05'),
(2, 'Alan Turing ', 'alan@Example.ORG', 'Manchester', '2024-01-20'),
(3, 'Grace Hopper', 'grace@navy.mil', 'New York', '2024-03-14'),
(4, 'Katherine Johnson', 'kj@nasa.gov', 'Hampton', '2024-06-11');
INSERT INTO orders VALUES
(101, 1, 42.507, '2024-07-01 09:30:00'),
(102, 1, 19.949, '2024-07-15 14:05:00'),
(103, 2, 128.250, '2024-06-20 11:00:00'),
(104, 3, 7.335, '2024-07-03 16:45:00'),
(105, 4, 256.000, '2024-05-28 08:15:00');Notice the deliberately messy data: some names have stray leading or trailing spaces, and the emails mix upper and lower case. Real data looks exactly like this, and scalar functions are how we tidy it.