String Functions

SQLite ships a solid set of built-in text functions for cleaning up and transforming string data — useful for recipe titles, usernames, and review comments across RecipeBox. We'll also cover string concatenation, which works noticeably differently in SQLite than it does in MySQL.

length(), upper(), lower()

-- How long is each title, and its upper/lowercase forms
SELECT
  title,
  length(title) AS title_length,
  upper(title) AS title_upper,
  lower(title) AS title_lower
FROM recipes;

substr()

substr(string, start, length) extracts a piece of a string. Positions are 1-based; the length argument is optional (omit it to take everything to the end), and a negative start counts backward from the end of the string. substring() is also accepted as an alias.

-- First 10 characters of each title, as a short preview
SELECT title, substr(title, 1, 10) AS preview FROM recipes;

-- The last 4 characters of each username (negative start counts from the end)
SELECT username, substr(username, -4) AS last_four FROM users;

trim(), ltrim(), rtrim()

These strip whitespace by default, or a specific set of characters when a second argument is given.

-- Clean up a comment that may have stray leading/trailing spaces
SELECT trim(comment) AS clean_comment FROM reviews;

-- ltrim / rtrim trim only from the left / right side respectively
SELECT ltrim('   Chicken Soup') AS left_trimmed;
SELECT rtrim('Chicken Soup   ') AS right_trimmed;

replace() and instr()

-- Swap "chicken" for "tofu" across every matching title
SELECT replace(title, 'Chicken', 'Tofu') AS veggie_version FROM recipes;

-- instr() returns the 1-based position of the first match, or 0 if not found
SELECT title, instr(title, 'Soup') AS soup_position FROM recipes;

The || Concatenation Operator

This is a common point of confusion coming from MySQL: MySQL joins strings with the CONCAT() function, but SQLite (like PostgreSQL and Oracle) uses the || operator instead. SQLite does not have a CONCAT() function built in — || is the primary way to concatenate.

-- Build a "title by author" display string with ||, not CONCAT()
SELECT r.title || ' by ' || u.username AS display_name
FROM recipes r
JOIN users u ON u.id = r.user_id;

printf() / format() for Complex Formatting

For anything beyond simple joining — padding numbers, fixed decimal places, mixed-type formatting — SQLite provides printf() (aliased as format()), which works like C's printf with format specifiers such as %s, %d, and %.2f.

-- Format a rating to exactly 2 decimal places inside a sentence
SELECT printf('%s is rated %.2f out of 5', title, rating) AS summary
FROM recipes;