Section 9: SQL Data Manipulation Functions

A SQL Functions Cheat-Sheet

This section provides a simple listing of various built-in SQL functions that enable you to manipulate and transform data directly within your queries. These functions allow you to perform a wide variety of calculations and data manipulations. If you are familiar with Microsoft Excel and its extensive range of functions, SQL offers similar processing capabilities for massive datasets. This list is non-exhaustive, links to various database engine’s function documentation is provided in the “Additional Resources” Section.

§1 Numeric / Mathematical Functions #

These functions perform mathematical operations on numeric data types, returning a number unless otherwise noted. They are useful for general calculations and statistical analysis within your SQL queries.

Sqlite is a much more limited database engine compared to Postgres or MySQL, many of these functions are not supported in SQLite.

abs(x): Returns the absolute value of x.

SELECT abs(-10);
-- Returns `10`

cbrt(dp): Returns the cube root of a double-precision number dp.

SELECT cbrt(27);
-- Returns `3`

ceil(dp/numeric)/ceiling(dp/numeric): Returns the smallest integer greater than or equal to the given number. This is useful for rounding up.

SELECT ceil(4.2);
-- Returns `5`

pi() Returns the value of pi as a double-precision number.

SELECT pi();
-- Returns `3.141592653589793`

degrees(rad): Converts radians to degrees.

SELECT degrees(pi()/2);
-- Returns `90`

radians(deg): Converts degrees to radians.

SELECT radians(180);
-- Returns `3.141592653589793`

exp(x): Returns e (Euler’s number, approximately 2.71828) raised to the power of x.

SELECT exp(1);
-- Returns `2.718281828459045`

ln(x): Returns the natural logarithm of x (base e).

SELECT ln(2.71828);
-- Returns `0.999999...` (approximately 1)

log(x): Returns the natural logarithm of x (base e).

SELECT log(10);
-- Returns `2.302585092994046`

log10(x): Returns the base-10 logarithm of x.

SELECT log10(100);
-- Returns `2`

log(b,x): Returns the logarithm of x to the base b.

SELECT log(2, 8);
-- Returns `3`

mod(a,b): Returns the remainder of a divided by b (same as a % b).

SELECT mod(10, 3);
-- Returns `1`
SELECT 7 % 4;
-- Returns `3`

power(a,b)/pow(a,b): Returns a raised to the power of b (same as a ** b).

SELECT power(2, 3);
-- Returns `8`
SELECT 2 ** 3;
-- Returns `8`

round(dp/numeric [,s]): Rounds a number to s fractional digits. If s is omitted, it rounds to the nearest integer.

SELECT round(123.456, 2); 
-- Returns `123.46
SELECT round(123.456); 
-- Returns `123

trunc(dp/numeric [,s]): Truncates a number to s fractional digits. If s is omitted, it truncates to an integer (removes the fractional part).

SELECT trunc(123.456, 2);
-- Returns `123.45`
SELECT trunc(123.452, 2);
-- Returns `123.45`
SELECT trunc(123.456);
-- Returns `123`

sign(x): Returns -1 if x is negative, 0 if x is zero, and 1 if x is positive.

SELECT sign(-8), sign(0), sign(5);
-- Returns `-1`, `0`, `1`

sqrt(x): Returns the square root of x.

SELECT sqrt(25);
-- Returns `5`

random(): Returns a random double-precision value in the range 0.0 <= v < 1.0. Each call within a query will produce a different value in most cases.

SELECT random();
-- Returns a value like `0.87654321`

setseed(dp): (Postgres only) Sets the seed for the random() function in the current session. Using the same seed will produce the same sequence of random numbers. Useful if you need random-looking numbers that are consistent across queries/sessions.

SELECT setseed(0.5); SELECT random(), random();
-- The subsequent `random()` calls will be predictable if the seed is reset.

§2 String Manipulation Functions #

These functions allow you to process, transform, and extract information from text strings. They are essential for data cleaning, formatting, searching, and parsing text-based data.

length(str): Returns the number of characters in a string.

SELECT length('Hello World');
-- Returns `11`

octet_length(str): Returns the number of bytes in a string. This can differ from length for multi-byte character encodings.

SELECT octet_length('😀');
-- Returns `4` (emojis use multiple bytes)

lower(str): Converts a string to lowercase.

SELECT lower('HELLO World');
-- Returns `'hello world'`

upper(str): Converts a string to uppercase.

SELECT upper('Hello World');
-- Returns `'HELLO WORLD'`

initcap(str): Converts the first letter of each word in a string to uppercase and the rest to lowercase.

SELECT initcap('hello world');
-- Returns `'Hello World'`

trim(str): Removes specified whitespace from both ends (both) of a string.

SELECT trim('   Hello World   ');
-- Returns `'Hello World'`

Trim specific characters:

SELECT trim('aaahello there','aaa'); -- sqlite
SELECT trim(BOTH 'a' FROM 'aaahello there'); -- postgres 
-- Returns `'hello there'`

ltrim(str [,chars]): Removes specified characters from the beginning (left) of a string.

SELECT ltrim('  Hello');
-- Returns `'Hello'`

rtrim(str [,chars]): Removes specified characters from the end (right) of a string.

SELECT rtrim('Hello  ');
-- Returns `'Hello'`

btrim(str [,chars]): Removes specified characters from both ends of a string

SELECT btrim('__Hello__', '_');
-- Returns `'Hello'`

lpad(str, len [,fill]): Pads a string on the left to a specified len with a fill character (default is space). Useful for fixed-width formatting.

SELECT lpad('123', 5, '0');
-- Returns `'00123'`

rpad(str, len [,fill]): Pads a string on the right to a specified len with a fill character (default is space).

SELECT rpad('abc', 5, '-');
-- Returns `'abc--'`

left(str, n): Returns the first n characters of a string.

SELECT left('Database', 4);
-- Returns `'Data'`

right(str, n): Returns the last n characters of a string.

SELECT right('Database', 4);
-- Returns `'base'`

substring(str [from int] [for int]): Extracts a substring from str starting at a given 1-based position from int for a specified for int length (SQL standard syntax).

SELECT substring('PostgreSQL' from 5 for 4);  -- postgres
SELECT substring('PostgreSQL', 5, 4);  -- sqlite
-- Returns `'greS'`

position(substr in str)/strpos(str, substr): Returns the 1-based starting index of the first occurrence of substr within str. Returns 0 if not found.

SELECT position('World' in 'Hello World');  -- postgres
SELECT strpos('Hello World', 'World');  -- postgres alternate
SELECT instr('Hello World', 'World');  -- sqlite
-- Returns `7`

replace(str, old, new): Replaces all occurrences of old substring with new substring in str.

SELECT replace('Hello World', 'o', '0');
-- Returns `'Hell0 W0rld'`

concat(str1, str2…): Concatenates two or more strings. NULL arguments are ignored.

SELECT concat('Hello', ' ', 'World');
-- Returns `'Hello World'`

concat_ws(sep, str…): Concatenates two or more strings with a separator sep. NULL arguments are ignored.

SELECT concat_ws(', ', 'Apple', 'Banana', 'Cherry');
-- Returns `'Apple, Banana, Cherry'`

format(fmt, args…): Formats a string using sprintf-style format specifiers (e.g., %s for string, %d for integer).

SELECT format('User %s has %d orders.', 'Alice', 5);
-- Returns `'User Alice has 5 orders.'`

repeat(str, n): Repeats a string n times.

SELECT repeat('SQL', 3);
-- Returns `'SQLSQLSQL'`

reverse(str): Reverses a string.

SELECT reverse('food');
-- Returns `'doof'`

split_part(str, delim, n): Splits a string by a delimiter and returns the n-th piece (1-indexed).

SELECT split_part('apple,banana,cherry', ',', 2);
-- Returns `'banana'`

§2.1 Regex string Manipulaton Functions #

These functions are sepcific to Postgres.
SQLite does not support most regular expression-functions. MySQL regex support is not as extensive as Postgres.

substring(str from pattern): Extracts a substring from str that matches a POSIX regular expression pattern.

SELECT substring('My email is <mailto:user@example.com>' from '([a-z]+@[a-z]+\.[a-z]+)');
-- Returns `'user@example.com'`

regexp_replace(str, pattern, repl [,flags]): Replaces substrings matching a regular expression pattern with repl. flags can include g for global replacement.

SELECT regexp_replace('123-456-7890', '(\d{3})-(\d{3})-(\d{4})', '($1) $2-$3');
-- Returns `'(123) 456-7890'`

regexp_match(str, pattern [,flags]): Returns a text[] array of captured substrings from a regular expression match. Returns NULL if no match.

SELECT regexp_match('My order ID is #12345.', '#(\d+)\.');
-- Returns `{"12345"}`

regexp_split_to_array(str, pattern): Splits a string into an array of text based on a regular expression pattern.

SELECT regexp_split_to_array('one, two, three', ', ');
-- Returns `{"one", "two", "three"}`

regexp_split_to_table(str, pattern): Splits a string into a set of text rows based on a regular expression pattern. This is a set-returning function, meaning it can produce multiple rows from a single input row.

SELECT * FROM regexp_split_to_table('apple;orange;grape', ';');
-- Returns three rows: `'apple'`, `'orange'`, `'grape'`

§3 Date / Time Functions #

These functions help you work with dates, times, or timestamps, including retrieving current values and performing arithmetic. Date and time manipulation are useful for reporting and analyzing time-series data.

§3.1 Current-Moment Functions #

These functions return the current date, time, or timestamp. Understanding their nuances is important for consistent results, especially in transactions.

Each Database engine supports many data/time manipulation functions, this section is limited to commonly supported functions. Read the linked docs in the “Additional Resources” section for additional datetime functions for each DBMS.

SQLITE Only:

SELECT current_timestamp;
-- Returns a similar timestamp to `now()`

current_date: Returns the current date.

SELECT current_date;
-- Returns something like `'2025-10-01'`

current_time: Returns the current time with time zone.

SELECT current_time;
-- Returns something like `'21:35:01.123456-07:00'`

§3.2 DateTime Extraction / Arithmetic Functions #

These functions allow you to extract parts of date/time values or perform calculations, which is necessary for grouping data by time periods, calculating durations, or filtering based on specific dates.

Postgres date_part(field, ts): Extracts a specific field from a timestamp ts. Common fields include:

SQLite strftime(field, ts) format specifiers:

Examples:

SELECT date_part('year', '2023-10-26 10:30:00'::timestamp); --postgres
SELECT strftime('%Y', '2023-10-26 10:30:00');  -- sqlite
--Returns '2023'

SELECT date_part('month', '2023-10-26'::date); --postgres
SELECT strftime('%m', '2023-10-26');  -- sqlite
--Returns '10'

SELECT date_part('dow', '2023-10-26'::date); --postgres
SELECT strftime('%w', '2023-10-26');  -- sqlite
--Returns '4'

SELECT date_part('doy', '2023-10-26'::date); --postgres
SELECT strftime('%j', '2023-10-26');  -- sqlite
--Returns '299'

SELECT date_part('week', '2023-10-26'::date); --postgres
SELECT strftime('%W', '2023-10-26');  -- sqlite
--Returns '43'

SELECT date_part('epoch', '2023-10-26 00:00:00'::timestamp); --postgres
SELECT strftime('%s', '2023-10-26 00:00:00');  -- sqlite
--Returns '1698278400'

SELECT date_part('millisecond', '2023-10-26 10:30:45.123'::timestamp); --postgres
SELECT CAST((strftime('%f','2023-10-26 10:30:45.123') * 1000) % 1000 AS INTEGER);  -- sqlite
--Returns '123'

SELECT date_part('hour', '2023-10-26 10:30:00'::timestamp); --postgres
SELECT strftime('%H', '2023-10-26 10:30:00');  -- sqlite
--Returns '10'

SELECT date_part('minute', '2023-10-26 10:30:00'::timestamp); --postgres
SELECT strftime('%M', '2023-10-26 10:30:00');  -- sqlite
--Returns '30'

SELECT date_part('second', '2023-10-26 10:30:45'::timestamp); --postgres
SELECT strftime('%S', '2023-10-26 10:30:45');  -- sqlite
--Returns '45'

PostgreSQL TO_CHAR(value, format): Formats a timestamp as a string using specified patterns. Common patterns include:

SQLite strftime(format, ts): Formats timestamps using C-style specifiers. Common specifiers include:

Examples:

SELECT TO_CHAR('2023-10-26'::date, 'YYYY-MM'); --postgres
SELECT strftime('%Y-%m', '2023-10-26');  -- sqlite
--Returns '2023-10'

SELECT TO_CHAR('2023-10-26 14:30:45'::timestamp, 'HH24:MI:SS'); --postgres
SELECT strftime('%H:%M:%S', '2023-10-26 14:30:45');  -- sqlite
--Returns '14:30:45'

SELECT TO_CHAR('2023-10-26'::date, 'Dy, Mon DD'); --postgres
--Returns 'Thu, Oct 26' (locale-dependent)
-- SQLite requires manual mapping:
SELECT CASE strftime('%w', '2023-10-26') 
       WHEN '0' THEN 'Sun' WHEN '1' THEN 'Mon' WHEN '2' THEN 'Tue'
       WHEN '3' THEN 'Wed' WHEN '4' THEN 'Thu' WHEN '5' THEN 'Fri' 
       ELSE 'Sat' END || ', ' || 
       CASE strftime('%m', '2023-10-26') 
       WHEN '01' THEN 'Jan' WHEN '02' THEN 'Feb' WHEN '03' THEN 'Mar'
       WHEN '04' THEN 'Apr' WHEN '05' THEN 'May' WHEN '06' THEN 'Jun'
       WHEN '07' THEN 'Jul' WHEN '08' THEN 'Aug' WHEN '09' THEN 'Sep'
       WHEN '10' THEN 'Oct' WHEN '11' THEN 'Nov' ELSE 'Dec' END || ' ' || 
       strftime('%d', '2023-10-26');
--Returns 'Thu, Oct 26'

SELECT TO_CHAR('2023-10-26 10:30:45.123'::timestamp, 'MS'); --postgres
SELECT SUBSTR(strftime('%f', '2023-10-26 10:30:45.123'), 3, 3);  -- sqlite
--Returns '123'

§4 Additional Resources #

§5 Questions #

Use this text box to test queries for the questions.
For these questions, I do not want the SQL outputs, only the SQL query.

Remember: this browser instance uses SQLite, not all functions available to Postgres and MySQL are supported.

  1. SQLite random() returns a huge signed integer. Write a query that produces a random() integer between 1 and 100 (inclusive).
  1. Return the absolute value of every number in the numbers table.
  1. For every title in the titles table, return the original title, the title in lowercase, and the title in uppercase.
  1. Round 324.565 to 2 decimal places using a SQL function.
  1. Return both the length and octet (byte) length of this unicode character sequence using a function (don’t include quotes) ‘⌘―👍🏼’.
  1. Return this string with the leading and trailing underscores removed (using a function): __init__.
  1. From the string SQLiteTest: extract characters 3-6 using a function.
  1. In the string hello SQL world: replace every lowercase ’l’ with ‘L’ using a function.
  1. Return one row with two columns: today’s date and the current time of day.
  1. For the timestamp literal 2025-07-04 12:00:01 return the following in a single row: