String and date functions · SQL

Functions take values and return new ones. You've already used ROUND, COUNT and SUM; these are the everyday functions for text, numbers and dates.

Text

SQL

SELECT
  name,
  UPPER(name)        AS upper_name,
  LOWER(name)        AS lower_name,
  LENGTH(name)       AS characters,
  SUBSTR(name, 1, 3) AS first_three
FROM customers
LIMIT 4;

SUBSTR(text, start, length) counts from 1, not 0.

Combining them: split full names at the space, and build URL-friendly slugs:

SQL

SELECT
  name,
  SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
  SUBSTR(name, INSTR(name, ' ') + 1)    AS last_name,
  LOWER(REPLACE(name, ' ', '-'))        AS slug
FROM customers;

INSTR(text, find) gives the position of find (or 0 if it's missing), and REPLACE(text, old, new) swaps every occurrence.

SQL

SELECT
  '[' || TRIM('   padded   ') || ']' AS trimmed,
  '[' || LTRIM('   left') || ']'     AS left_trimmed,
  'Order #' || 101                   AS joined_up;

Numbers

SQL

SELECT
  ROUND(2.567, 1)     AS rounded,
  ABS(-42)            AS absolute,
  7 / 2               AS integer_division,
  7 / 2.0             AS real_division,
  CAST(7 AS REAL) / 2 AS cast_first,
  7 % 3               AS remainder;

Dates

SQLite has no separate date type: dates are stored as text in YYYY-MM-DD form, and its date functions read and write that format.

SQL

SELECT
  DATE('now')                                               AS today,
  DATE('2024-06-01', '+30 days')                            AS thirty_days_later,
  DATE('2024-06-01', 'start of month', '+1 month', '-1 day') AS end_of_june,
  STRFTIME('%Y', '2024-06-01')                              AS year,
  STRFTIME('%m', '2024-06-01')                              AS month;

Days between two dates, with JULIANDAY:

SQL

SELECT
  name,
  joined,
  CAST(JULIANDAY('2024-12-31') - JULIANDAY(joined) AS INTEGER) AS days_as_customer
FROM customers
WHERE joined IS NOT NULL
ORDER BY days_as_customer DESC;

Orders per month:

SQL

SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month;

Every database spells them differently

The ideas are universal, but the names vary. A cheat sheet:

Task	SQLite	MySQL	PostgreSQL	SQL Server
join text	`a \|\| b`	`CONCAT(a, b)`	`a \|\| b`	`a + b`
length	`LENGTH(s)`	`CHAR_LENGTH(s)`	`LENGTH(s)`	`LEN(s)`
part of a text	`SUBSTR(s, 1, 3)`	`SUBSTRING(s, 1, 3)`	`SUBSTRING(s, 1, 3)`	`SUBSTRING(s, 1, 3)`
find in text	`INSTR(s, 'x')`	`LOCATE('x', s)`	`POSITION('x' IN s)`	`CHARINDEX('x', s)`
today	`DATE('now')`	`CURDATE()`	`CURRENT_DATE`	`CAST(GETDATE() AS DATE)`
add 7 days	`DATE(d, '+7 days')`	`DATE_ADD(d, INTERVAL 7 DAY)`	`d + INTERVAL '7 days'`	`DATEADD(day, 7, d)`
year of a date	`STRFTIME('%Y', d)`	`YEAR(d)`	`EXTRACT(YEAR FROM d)`	`YEAR(d)`