Built-in Functions

The previous lesson used three constructs that convert null values: COALESCE, NULLIF, and CASE. All three are scalar functions — they take a single row’s values and produce a single value, they do not change the result set’s row count, they only transform column values.

This lesson builds the rest of the same family: built-in functions that work on text, numbers, and dates. What is instructive about the family is not only what they do, but how variable their naming is. Built-in functions are the area where standard SQL and the engine dialect diverge most visibly.

The block below sets up the schema and sample data; every query in this lesson runs against the library.db file created in this block.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

Length: Characters or Bytes

A function that gives text’s length has to be clear about what it measures. The distinction built in the How Computers Work course appears directly here: a character can occupy more than one byte.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT LENGTH('Körlük') AS characters, LENGTH(CAST('Körlük' AS BLOB)) AS bytes;
SQL

characters  bytes
----------  -----
6           8    

The six-character word takes eight bytes: each of the letters ö and ü takes two bytes. Whether the length function counts characters or bytes depends on the engine; some engines have two separately named functions, others look at whether the type is text or binary data. In a field-limit check, this distinction turns directly into a bug — a check that measures in characters but stores in bytes overflows earlier than expected on Turkish text.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, LENGTH(title) AS length FROM book ORDER BY length DESC, title;
SQL

title              length
-----------------  ------
Tehlikeli Oyunlar  17    
Motherland Hotel   16    
The Book of Sand   16    
The Disconnected   16    
Silent House       12    
Blindness          9     
Yaban              5     

Cutting, Searching, Replacing

To take a piece of text, a substring function is used. The standard syntax and the common syntax diverge here.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT SUBSTRING('2024-01-09' FROM 1 FOR 4) AS year;
SQL

Parse error near line 3: near "FROM": syntax error
  SELECT SUBSTRING('2024-01-09' FROM 1 FOR 4) AS year;
                  error here ---^

The standard’s SUBSTRING(… FROM … FOR …) syntax is not recognized by this engine. The syntax the engine accepts is the form that separates arguments with commas.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT first_name, SUBSTR(registered_at,1,4) AS registration_year FROM member ORDER BY member_id;
SQL

first_name  registration_year
----------  -----------------
Alice       2023             
Ben         2023             
Clara       2024             
Derek       2024             
Grace       2024             
Owen        2025             

The name of the function that finds a substring’s position also depends on the engine. Instead of the standard’s POSITION(… IN …) syntax, this engine has a separately named function.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT email, INSTR(email,'@') AS position FROM member WHERE email IS NOT NULL ORDER BY member_id;
SQL

email               position
------------------  --------
[email protected]  6       
[email protected]    4       
[email protected]  6       
[email protected]   5       

Position starts at 1. String positions in SQL are one-based; the zero-based indexing habit from the Programming Fundamentals course does not apply here, and it is a frequent source of an off-by-one mistake.

Replacement and trimming functions are less variable.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, REPLACE(title,'Hotel','Inn') AS replaced FROM book
WHERE title LIKE '%Hotel%';
SELECT '[' || TRIM('   Blindness   ') || ']' AS trimmed;
SQL

title             replaced      
----------------  --------------
Motherland Hotel  Motherland Inn
trimmed    
-----------
[Blindness]

The trim function removes whitespace from both ends by default; there are also forms that state which characters to trim and that trim from only one end.

Number Functions and Division

Most numeric functions are uniform, but division behavior stands apart.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT ABS(-7) AS absolute, ROUND(2.555, 2) AS rounded,
       17 % 5 AS remainder, 17 / 5 AS quotient, 17.0 / 5 AS decimal_quotient;
SQL

absolute  rounded  remainder  quotient  decimal_quotient
--------  -------  ---------  --------  ----------------
7         2.56     2          3         3.4             

17 / 5 produced 3, 17.0 / 5 produced 3.4. What determines whether the division is an integer or a decimal is the type of the operands. This behavior depends on the engine: some engines produce a decimal for the division of two integers too. The portable syntax is to cast at least one operand to a decimal type before dividing.

The remainder operator can also be found in two forms, and the forms may not produce the same type.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT 17 % 5 AS with_operator, MOD(17,5) AS with_function;
SQL

with_operator  with_function
-------------  -------------
2              2.0          

The same number came back once as an integer and once as a decimal. This difference matters if the result is going to enter a comparison or a grouping.

There is a point worth watching in rounding too: decimal numbers cannot be represented exactly in binary — the conclusion of the floating-point lesson in the How Computers Work course holds here as well. In monetary calculations, trust is placed not in the rounding function, but in a decimal type.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, publication_year, publication_year % 10 AS last_digit FROM book
WHERE publication_year IS NOT NULL ORDER BY book_id;
SQL

title              publication_year  last_digit
-----------------  ----------------  ----------
Blindness          1995              5         
The Disconnected   1972              2         
The Book of Sand   1975              5         
Yaban              1932              2         
Silent House       1983              3         
Tehlikeli Oyunlar  1973              3         

Date Functions

Date is the area where the standard is applied the least. The standard defines a predicate-like syntax for extracting a part from a date, and an interval type for adding a duration to a date. Neither is present in every engine.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT EXTRACT(YEAR FROM pickup_date) AS year FROM loan;
SQL

Parse error near line 3: near "FROM": syntax error
  SELECT EXTRACT(YEAR FROM pickup_date) AS year FROM loan;
                      ^--- error here

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT pickup_date + INTERVAL '14' DAY AS due_date FROM loan;
SQL

Parse error near line 3: near "DAY": syntax error
  SELECT pickup_date + INTERVAL '14' DAY AS due_date FROM loan;
                       error here ---^

Neither standard syntax worked on this engine. The equivalents the engine offers carry different names.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT loan_id, pickup_date,
       strftime('%Y', pickup_date) AS year,
       date(pickup_date,'+14 days')  AS due_date
FROM loan WHERE loan_id <= 4 ORDER BY loan_id;
SQL

loan_id  pickup_date  year  due_date  
-------  -----------  ----  ----------
1        2025-01-10   2025  2025-01-24
2        2025-02-02   2025  2025-02-16
3        2025-02-11   2025  2025-02-25
4        2025-03-01   2025  2025-03-15

Taking the difference between two dates in days also requires engine-specific syntax.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT loan_id, pickup_date, return_date,
       CAST(julianday(return_date) - julianday(pickup_date) AS INTEGER) AS days
FROM loan WHERE return_date IS NOT NULL AND loan_id <= 5 ORDER BY loan_id;
SQL

loan_id  pickup_date  return_date  days
-------  -----------  -----------  ----
1        2025-01-10   2025-01-24   14  
2        2025-02-02   2025-02-20   18  
4        2025-03-01   2025-03-15   14  
5        2025-03-18   2025-04-02   15  

The third loan record is not in the list: because its return date is null, the difference cannot be computed, and the condition eliminated it. The null value rule holds here too.

The engine-dependence of date functions has a practical consequence: once date arithmetic gets embedded inside a query, the query becomes unportable. When portability is required, date arithmetic either moves to the application layer or the engine-specific part gets gathered in a single place. Whether a date column carries a time zone also depends on the engine and the type; a day computation done on a column without a time zone can drift by a day at a time zone boundary.

Summary

• Scalar functions run per row and do not change the result set’s row count.
• Whether the length function counts characters or bytes depends on the engine; the two diverge on non-ASCII text.
• String positions are one-based; the standard syntax for substring and position functions is not present in every engine.
• The type of the operands determines whether division is an integer or a decimal, and this behavior depends on the engine.
• Date functions are the area that diverges from the standard the most; the syntax for extracting a part and adding a duration changes from engine to engine.
• The null value rule holds in date arithmetic too: a difference with one blank end cannot be computed.

Next Step

Up to this point, every query has read from a single table. Yet in a normalized schema, meaningful questions do not fit into a single table: the answer to “which member borrowed which book” is spread across three tables, because the loan table holds only ids. The next topic builds the clause that joins tables, and its first lesson takes up the intersection of matching rows — the inner join.