Chapter 4 · Functions and Summaries
Text, Number and Date Functions, CAST and COALESCE
- Page 13 of 22
- 17 min read
Raw columns are rarely in the shape you need. Emails come with stray spaces and capital letters, dates are stored as text, a price must be shown in thousands, a missing city should read "Unknown". SQL functions fix all of this inside the query, row by row, before the data ever reaches pandas or a model. Much of the "feature engineering" in real ML projects is exactly this: an email domain, the length of a review, the month of an order, the number of days someone has been a customer.
Functions are also where SQL dialects differ most. This page uses SQLite for the main examples and shows the MySQL and PostgreSQL versions next to them, run on real servers.
What you will learn
- Text functions: concatenation,
LENGTH,UPPER/LOWER,SUBSTR,INSTR,TRIM,REPLACE, and their server equivalents. - Number functions:
ROUND,ABS,CEIL/FLOOR, and the integer division trap. - Date functions:
date(),strftime()andjulianday()in SQLite;EXTRACT,DATEDIFF,INTERVALon the servers. - Type conversion with
CAST, and NULL handling withCOALESCEandNULLIF(safe division). - Writing your own SQL function in Python.
What a function is
A function takes values and returns one value: UPPER('dhaka') returns 'DHAKA'. Used on a column, it runs once for every row. These are scalar functions: one row in, one value out. Functions that squeeze many rows into one value (COUNT, SUM, AVG) are aggregates, the subject of the next page. You can use a function anywhere a value can go: in SELECT, WHERE, ORDER BY, inside another function.
Text functions
Here are the everyday ones on the customers table. SUBSTR(text, start, length) counts from 1, and INSTR(text, part) gives the position where part starts (0 if it is absent), which lets you cut a string at a character:
SELECT name,
UPPER(name) AS upper_name,
LENGTH(name) AS len,
SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers
WHERE customer_id <= 3
ORDER BY customer_id;+---------------+---------------+-----+------------+-------------+
| name | upper_name | len | first_name | domain |
+---------------+---------------+-----+------------+-------------+
| Nadia Rahman | NADIA RAHMAN | 12 | Nadia | example.com |
| Tanvir Ahmed | TANVIR AHMED | 12 | Tanvir | example.com |
| Farhana Akter | FARHANA AKTER | 13 | Farhana | example.com |
+---------------+---------------+-----+------------+-------------+Cleaning messy input is the most common use. TRIM removes spaces at both ends (LTRIM/RTRIM one end), LOWER makes text comparable, REPLACE swaps every occurrence of one string for another:
SELECT '[' || TRIM(' Dhaka ') || ']' AS trimmed,
LOWER(TRIM(' Nadia@Example.COM ')) AS clean_email,
REPLACE(REPLACE('+880 1711-223344', ' ', ''), '-', '') AS phone;+---------+-------------------+----------------+
| trimmed | clean_email | phone |
+---------+-------------------+----------------+
| [Dhaka] | nadia@example.com | +8801711223344 |
+---------+-------------------+----------------+Joining strings, and what NULL does to it
The standard operator is ||. If any piece is NULL, the whole result is NULL. Sadia has no city, so her label vanishes; COALESCE (below) fixes it:
SELECT name,
name || ' (' || city || ')' AS label,
name || ' (' || COALESCE(city, 'Unknown') || ')' AS label_fixed
FROM customers
WHERE customer_id IN (4, 5)
ORDER BY customer_id;+-----------------+----------------------+---------------------------+
| name | label | label_fixed |
+-----------------+----------------------+---------------------------+
| Rafiq Islam | Rafiq Islam (Sylhet) | Rafiq Islam (Sylhet) |
| Sadia Chowdhury | NULL | Sadia Chowdhury (Unknown) |
+-----------------+----------------------+---------------------------+CONCAT() treats NULL as an empty string in SQLite (3.44+) and PostgreSQL, but returns NULL in MySQL. And in MySQL, || is not concatenation at all: by default it means logical OR, so 'a' || 'b' is 0. Use CONCAT in MySQL. CONCAT_WS(separator, …) joins with a separator and skips NULLs in all three:
-- MySQL
SELECT 'a' || 'b' AS pipes,
CONCAT('a', 'b') AS concat_ab,
CONCAT('a', NULL, 'b') AS concat_null,
CONCAT_WS('-', 'a', NULL, 'b') AS with_sep;+-------+-----------+-------------+----------+
| pipes | concat_ab | concat_null | with_sep |
+-------+-----------+-------------+----------+
| 0 | ab | NULL | a-b |
+-------+-----------+-------------+----------+Server-only helpers, and characters vs bytes
MySQL and PostgreSQL have LEFT and RIGHT (SQLite does not; use SUBSTR(s, 1, n) and SUBSTR(s, -n)). PostgreSQL finds a position with POSITION(… IN …) or STRPOS instead of INSTR, and SPLIT_PART splits in one step:
-- PostgreSQL
SELECT LEFT(name, 5) AS left_5,
RIGHT(email, 11) AS right_11,
SUBSTRING(name FROM 1 FOR 3) AS first_3,
POSITION('@' IN email) AS at_pos,
SPLIT_PART(email, '@', 2) AS domain
FROM customers
WHERE customer_id = 1;+--------+-------------+---------+--------+-------------+
| left_5 | right_11 | first_3 | at_pos | domain |
+--------+-------------+---------+--------+-------------+
| Nadia | example.com | Nad | 6 | example.com |
+--------+-------------+---------+--------+-------------+Length is subtle with Bangla. In UTF-8, each Bangla character takes 3 bytes. SQLite's and PostgreSQL's LENGTH count characters, but MySQL's LENGTH counts bytes; use CHAR_LENGTH there:
-- MySQL
SELECT LENGTH('ঢাকা') AS length_bytes, CHAR_LENGTH('ঢাকা') AS chars;+--------------+-------+
| length_bytes | chars |
+--------------+-------+
| 12 | 4 |
+--------------+-------+A "character" here is a Unicode code point, so a vowel sign such as া counts on its own: ঢাকা is 4 characters, not 2 letters. This matters when you cut text to fit a column or an LLM prompt limit: cutting at a byte count can split a character in half, and even a character cut can separate a consonant from its vowel sign.
Number functions and the integer division trap
ROUND(x, digits), ABS(x) and % (remainder) work everywhere. The trap is division: when both sides are integers, SQLite, PostgreSQL and SQL Server do integer division and throw the fraction away. Showing prices in thousands of taka:
SELECT name, price,
price / 1000 AS k_wrong,
ROUND(price / 1000.0, 1) AS k_taka
FROM products
WHERE product_id IN (1, 4, 5)
ORDER BY product_id;+---------------------+-------+---------+--------+
| name | price | k_wrong | k_taka |
+---------------------+-------+---------+--------+
| Python Crash Course | 1200 | 1 | 1.2 |
| Wireless Mouse | 900 | 0 | 0.9 |
| 27-inch Monitor | 18500 | 18 | 18.5 |
+---------------------+-------+---------+--------+The mouse costs "0 thousand". Make one side a decimal (1000.0, price * 1.0 or CAST(price AS REAL)) and the division keeps its fraction. MySQL is the odd one out: / always gives a decimal, and DIV is its integer division. CEIL and FLOOR round up and down; they exist on every server, but in SQLite only when it was compiled with its maths functions, so do not rely on them there:
-- MySQL
SELECT 7 / 2 AS divide, 7 DIV 2 AS int_divide, 7 % 2 AS remainder,
CEIL(2.1) AS ceil_, FLOOR(-2.1) AS floor_, ROUND(AVG(price), 2) AS avg_price
FROM products;+--------+------------+-----------+-------+--------+-----------+
| divide | int_divide | remainder | ceil_ | floor_ | avg_price |
+--------+------------+-----------+-------+--------+-----------+
| 3.5000 | 3 | 1 | 3 | -3 | 5254.55 |
+--------+------------+-----------+-------+--------+-----------+-- PostgreSQL
SELECT 7 / 2 AS divide, 7 / 2.0 AS decimal_divide, 7 % 2 AS remainder,
CEIL(2.1) AS ceil_, FLOOR(-2.1) AS floor_, ROUND(AVG(price), 2) AS avg_price
FROM products;+--------+--------------------+-----------+-------+--------+-----------+
| divide | decimal_divide | remainder | ceil_ | floor_ | avg_price |
+--------+--------------------+-----------+-------+--------+-----------+
| 3 | 3.5000000000000000 | 1 | 3 | -3 | 5254.55 |
+--------+--------------------+-----------+-------+--------+-----------+PostgreSQL has one more catch: ROUND(x, 2) works on NUMERIC but not on floating-point values. Cast first, ROUND(x::numeric, 2):
-- PostgreSQL
SELECT ROUND(CAST(2.345 AS DOUBLE PRECISION), 2);Error: function round(double precision, integer) does not existDate functions
SQLite has no real date type: dates are text in ISO format 'YYYY-MM-DD' (see Data Types, NULL and Choosing the Right Type), and functions interpret that text. date(value, modifier, …) does date arithmetic, strftime(format, value) pulls out parts (%Y year, %m month, %d day, %w weekday with 0 = Sunday):
SELECT order_id, order_date,
strftime('%Y-%m', order_date) AS month,
strftime('%w', order_date) AS weekday,
date(order_date, '+7 days') AS due_date,
date(order_date, 'start of month') AS month_start
FROM orders
WHERE order_id IN (1, 7, 13)
ORDER BY order_id;+----------+------------+---------+---------+------------+-------------+
| order_id | order_date | month | weekday | due_date | month_start |
+----------+------------+---------+---------+------------+-------------+
| 1 | 2026-01-12 | 2026-01 | 1 | 2026-01-19 | 2026-01-01 |
| 7 | 2026-03-15 | 2026-03 | 0 | 2026-03-22 | 2026-03-01 |
| 13 | 2026-06-02 | 2026-06 | 2 | 2026-06-09 | 2026-06-01 |
+----------+------------+---------+---------+------------+-------------+To count days between two dates, subtract their julianday() values (a day number). This is a classic ML feature, customer tenure. Notice the fixed "as of" date: date('now') exists, but a feature that changes every time you run the query cannot be reproduced, and in a training set it can leak information from the future. Always compute features as of a stated date:
SELECT customer_id, joined_on,
CAST(julianday('2026-06-30') - julianday(joined_on) AS INTEGER) AS tenure_days
FROM customers
ORDER BY tenure_days DESC, customer_id
LIMIT 4;+-------------+------------+-------------+
| customer_id | joined_on | tenure_days |
+-------------+------------+-------------+
| 1 | 2025-11-03 | 239 |
| 2 | 2025-12-14 | 198 |
| 3 | 2026-01-09 | 172 |
| 4 | 2026-01-22 | 159 |
+-------------+------------+-------------+On the servers dates are real types with their own functions. Note the month arithmetic: one month after 31 January is 28 February on MySQL and PostgreSQL, but SQLite overflows into March:
SELECT date('2026-01-31', '+1 month') AS sqlite_plus_month;+-------------------+
| sqlite_plus_month |
+-------------------+
| 2026-03-03 |
+-------------------+-- MySQL
SELECT DATEDIFF('2026-06-30', '2026-01-12') AS days_between,
DATE_ADD('2026-01-31', INTERVAL 1 MONTH) AS plus_month,
'2026-06-02' + INTERVAL 7 DAY AS plus_week,
EXTRACT(MONTH FROM '2026-06-02') AS month_no,
DATE_FORMAT('2026-06-02', '%Y-%m') AS ym;+--------------+------------+------------+----------+---------+
| days_between | plus_month | plus_week | month_no | ym |
+--------------+------------+------------+----------+---------+
| 169 | 2026-02-28 | 2026-06-09 | 6 | 2026-06 |
+--------------+------------+------------+----------+---------+-- PostgreSQL
SELECT DATE '2026-06-30' - DATE '2026-01-12' AS days_between,
DATE '2026-01-31' + INTERVAL '1 month' AS plus_month,
DATE '2026-06-02' + 7 AS plus_week,
EXTRACT(MONTH FROM DATE '2026-06-02') AS month_no,
TO_CHAR(DATE '2026-06-02', 'YYYY-MM') AS ym;+--------------+---------------------+------------+----------+---------+
| days_between | plus_month | plus_week | month_no | ym |
+--------------+---------------------+------------+----------+---------+
| 169 | 2026-02-28 00:00:00 | 2026-06-09 | 6 | 2026-06 |
+--------------+---------------------+------------+----------+---------+In PostgreSQL, date minus date is a whole number of days, date plus an integer is a date, and date plus an INTERVAL becomes a timestamp. DATE_PART('month', d) is PostgreSQL's older spelling of EXTRACT.
CAST: changing a value's type
Data loaded from CSV files or APIs often arrives as text. CAST(value AS type) converts it; PostgreSQL also has the short form value::type, MySQL and SQL Server also have CONVERT. SQLite's typeof() shows what you got. The dialects disagree sharply about bad input:
SELECT CAST('42' AS INTEGER) + 1 AS ok,
CAST('12abc' AS INTEGER) AS partly_number,
CAST('abc' AS INTEGER) AS not_number,
CAST(3.99 AS INTEGER) AS truncated,
typeof(CAST(1200 AS REAL)) AS type_after;+----+---------------+------------+-----------+------------+
| ok | partly_number | not_number | truncated | type_after |
+----+---------------+------------+-----------+------------+
| 43 | 12 | 0 | 3 | real |
+----+---------------+------------+-----------+------------+-- PostgreSQL
SELECT CAST('12abc' AS INTEGER);Error: invalid input syntax for type integer: "12abc"SQLite (and MySQL, with only a warning) quietly turns '12abc' into 12 and 'abc' into 0. PostgreSQL refuses. For data you will train on, the loud failure is the friend: a silent 0 becomes a fake data point. Also note that CAST(3.99 AS INTEGER) cuts to 3; use ROUND if you want 4.
COALESCE and NULLIF
COALESCE(a, b, c, …) returns the first value that is not NULL. It is how you give a default for missing data (MySQL's IFNULL and SQL Server's ISNULL are two-argument versions). Use it with thought: COALESCE(city, 'Unknown') is honest, but COALESCE(stock, 0) would claim the courses are sold out when NULL actually means "stock is not tracked".
NULLIF(a, b) does the reverse: it returns NULL when a equals b, otherwise a. Its main job is safe division. Say you log model evaluations, and one model has not been run on any question yet:
-- PostgreSQL
CREATE TABLE eval_runs (model TEXT PRIMARY KEY, correct INTEGER, attempted INTEGER);
INSERT INTO eval_runs VALUES ('small-v1', 41, 50), ('large-v2', 47, 50), ('new-v3', 0, 0);
SELECT model, correct * 100.0 / attempted AS accuracy_pct FROM eval_runs;Error: division by zero-- PostgreSQL
SELECT model,
ROUND(correct * 100.0 / NULLIF(attempted, 0), 1) AS accuracy_pct
FROM eval_runs
ORDER BY model;+----------+--------------+
| model | accuracy_pct |
+----------+--------------+
| large-v2 | 94.0 |
| new-v3 | NULL |
| small-v1 | 82.0 |
+----------+--------------+PostgreSQL stops the whole query at the first zero. With NULLIF, a zero denominator becomes NULL, and anything divided by NULL is NULL: "no accuracy yet", which is the truth. SQLite and MySQL return NULL for division by zero without being asked, so the problem hides there; write NULLIF anyway so the query is portable and the intent is visible.
Your own functions, from Python
When SQL has no function for what you need, Python's sqlite3 lets you register one. Here is a word counter, the kind of quick text feature you might compute for reviews or prompts:
from sqlhelp import con, show
def word_count(text):
return None if text is None else len(text.split())
con.create_function("word_count", 1, word_count, deterministic=True)
show("""
SELECT name, word_count(name) AS words, LENGTH(name) AS chars
FROM products
WHERE category_id = 1 OR product_id = 5
ORDER BY product_id
""")+---------------------------+-------+-------+
| name | words | chars |
+---------------------------+-------+-------+
| Python Crash Course | 3 | 19 |
| Hands-On Machine Learning | 3 | 25 |
| 27-inch Monitor | 2 | 15 |
+---------------------------+-------+-------+The function lives only in that Python connection: DB Browser or the sqlite3 shell will not know it. deterministic=True promises the same input always gives the same output, which lets SQLite use it in more places (such as indexes). MySQL and PostgreSQL have CREATE FUNCTION to store functions inside the database (see the advanced tutorial).
Dialect cheat sheet
| Task | SQLite | MySQL | PostgreSQL |
|---|---|---|---|
| Join text | a || b, concat() (3.44+) | CONCAT(a, b) | a || b, CONCAT(a, b) |
| Length in characters | LENGTH | CHAR_LENGTH | LENGTH |
| Find position | INSTR(s, x) | INSTR(s, x), LOCATE(x, s) | POSITION(x IN s), STRPOS(s, x) |
| First n characters | SUBSTR(s, 1, n) | LEFT(s, n) | LEFT(s, n) |
7 / 2 | 3 | 3.5000 | 3 |
| Add 7 days | date(d, '+7 days') | d + INTERVAL 7 DAY | d + 7, d + INTERVAL '7 days' |
| Days between | julianday(a) - julianday(b) | DATEDIFF(a, b) | a - b |
| Year-month text | strftime('%Y-%m', d) | DATE_FORMAT(d, '%Y-%m') | TO_CHAR(d, 'YYYY-MM') |
| Bad number text | Silently 0 or a prefix | Prefix, with a warning | Error |
Where this shows up in real systems
- Feature extraction: email domain, text length, tenure in days, order month and weekday are computed in SQL so that every model and dashboard uses the same definition.
- Join keys:
LOWER(TRIM(email))on both sides before matching customer lists from two systems. - Monitoring AI apps:
strftime('%Y-%m-%d', created_at)to group LLM calls by day, andNULLIFto compute error rates for days with no traffic.
Common mistakes
- Integer division.
price / 1000gives 0 for the mouse. Fix:price / 1000.0orCAST(price AS REAL) / 1000. - NULL swallowing a concatenation.
name || ' (' || city || ')'is NULL for Sadia. Fix:COALESCE(city, 'Unknown')inside, orCONCAT_WS. ||orLENGTHin MySQL.||means OR andLENGTHcounts bytes. Fix:CONCAT(a, b)andCHAR_LENGTH(s).- Using
date('now')in features. Results change every day and cannot be reproduced. Fix: a fixed as-of date, e.g.julianday('2026-06-30') - julianday(joined_on). - Trusting a silent CAST.
CAST('abc' AS INTEGER)is 0 in SQLite. Fix: check text before converting, e.g.WHERE value GLOB '[0-9]*' AND value NOT GLOB '*[^0-9]*', or load into PostgreSQL, which refuses bad values.
Try it yourself
- Easy: For every customer, show the name in capitals and the part of the email before the
@. Order bycustomer_id. - Medium: For each product, show the price with 15% VAT added, rounded to whole taka, and the price in thousands with one decimal. Only products under 3000 taka, cheapest first (break ties by id).
- Hard: Build one feature row per customer as of 2026-06-30:
customer_id,first_name,email_domain,city(or'Unknown'),joined_month(YYYY-MM) andtenure_days. Then register a Python functionmask_emailthat turnsnadia@example.cominton***@example.com(privacy matters before data leaves the database) and show it for customers 1 to 3.
Answers
-- Easy
SELECT customer_id, UPPER(name) AS name, SUBSTR(email, 1, INSTR(email, '@') - 1) AS user
FROM customers
ORDER BY customer_id;+-------------+-----------------+---------+
| customer_id | name | user |
+-------------+-----------------+---------+
| 1 | NADIA RAHMAN | nadia |
| 2 | TANVIR AHMED | tanvir |
| 3 | FARHANA AKTER | farhana |
| 4 | RAFIQ ISLAM | rafiq |
| 5 | SADIA CHOWDHURY | sadia |
| 6 | IMRAN HOSSAIN | imran |
| 7 | MITU DAS | mitu |
| 8 | KARIM UDDIN | karim |
+-------------+-----------------+---------+-- Medium
SELECT product_id, name, price,
CAST(ROUND(price * 1.15) AS INTEGER) AS price_with_vat,
ROUND(price / 1000.0, 1) AS price_k
FROM products
WHERE price < 3000
ORDER BY price, product_id;+------------+---------------------------+-------+----------------+---------+
| product_id | name | price | price_with_vat | price_k |
+------------+---------------------------+-------+----------------+---------+
| 4 | Wireless Mouse | 900 | 1035 | 0.9 |
| 11 | Gift Card | 1000 | 1150 | 1.0 |
| 1 | Python Crash Course | 1200 | 1380 | 1.2 |
| 8 | Laptop Stand | 1500 | 1725 | 1.5 |
| 9 | USB-C Hub | 2200 | 2530 | 2.2 |
| 2 | Hands-On Machine Learning | 2500 | 2875 | 2.5 |
+------------+---------------------------+-------+----------------+---------+-- Hard, part 1: the feature row
SELECT customer_id,
SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
SUBSTR(email, INSTR(email, '@') + 1) AS email_domain,
COALESCE(city, 'Unknown') AS city,
strftime('%Y-%m', joined_on) AS joined_month,
CAST(julianday('2026-06-30') - julianday(joined_on) AS INTEGER) AS tenure_days
FROM customers
ORDER BY customer_id;+-------------+------------+--------------+------------+--------------+-------------+
| customer_id | first_name | email_domain | city | joined_month | tenure_days |
+-------------+------------+--------------+------------+--------------+-------------+
| 1 | Nadia | example.com | Dhaka | 2025-11 | 239 |
| 2 | Tanvir | example.com | Chattogram | 2025-12 | 198 |
| 3 | Farhana | example.com | Dhaka | 2026-01 | 172 |
| 4 | Rafiq | example.com | Sylhet | 2026-01 | 159 |
| 5 | Sadia | example.com | Unknown | 2026-02 | 145 |
| 6 | Imran | example.com | Khulna | 2026-02 | 132 |
| 7 | Mitu | example.com | Dhaka | 2026-03 | 92 |
| 8 | Karim | example.com | Rajshahi | 2026-04 | 80 |
+-------------+------------+--------------+------------+--------------+-------------+# Hard, part 2: a Python function used from SQL
from sqlhelp import con, show
def mask_email(email):
if email is None or "@" not in email:
return email
user, domain = email.split("@", 1)
return user[0] + "***@" + domain
con.create_function("mask_email", 1, mask_email, deterministic=True)
show("SELECT customer_id, mask_email(email) AS email FROM customers WHERE customer_id <= 3 ORDER BY customer_id")+-------------+------------------+
| customer_id | email |
+-------------+------------------+
| 1 | n***@example.com |
| 2 | t***@example.com |
| 3 | f***@example.com |
+-------------+------------------+Summary
- Scalar functions turn each row's values into a new value; use them in
SELECT,WHEREandORDER BY. - Text:
||/CONCAT,LENGTH,UPPER/LOWER,SUBSTR,INSTR,TRIM,REPLACE; MySQL needsCONCATandCHAR_LENGTH. - Numbers:
ROUND,ABS,%; integer ÷ integer drops the fraction except in MySQL.CEIL/FLOORon the servers. - Dates:
date(),strftime(),julianday()in SQLite;DATEDIFF/INTERVAL/DATE_FORMATin MySQL; date subtraction,INTERVAL,EXTRACT,TO_CHARin PostgreSQL. Use a fixed as-of date for features. CASTconverts types (SQLite is lenient, PostgreSQL strict);COALESCEfills NULLs;NULLIF(x, 0)makes division safe; Python can add custom functions.
Next: Aggregates, GROUP BY and HAVING. So far every function worked on one row at a time. Next you will collapse many rows into totals, averages and counts, per customer, per product and per status, which is how raw orders become revenue figures.