SQL String Functions

SQL string functions let you inspect, clean, transform, and combine text data directly inside a query, without pulling rows back to application code first. They are the tools you reach for to normalize capitalization, strip stray whitespace, pull a username out of an email address, or glue a first and last name together into one display column. Because they run inside the database engine, string functions can appear almost anywhere in a query – the SELECT list, WHERE, ORDER BY, even GROUP BY – and understanding how they behave, especially around NULL values, will save you from some very confusing bugs.

Overview / How String Functions Work

A string function is a scalar function: it takes one or more input values and returns exactly one output value, computed independently for every row. This is different from an aggregate function like COUNT() or GROUP_CONCAT(), which collapses many rows into one value. When a string function appears in your SELECT list, the engine calls it once per row in the final result set, using the column values from that row.

Under the hood, SQLite stores text using the TEXT storage class, encoded as UTF-8 (or UTF-16, though UTF-8 is the default and far more common). Functions like LENGTH() count characters for text data – not raw bytes – which matters once you start working with multi-byte characters. SQLite’s built-in string function library is intentionally compact compared to MySQL, PostgreSQL, or SQL Server, which each add extra convenience functions (LEFT(), RIGHT(), CHARINDEX(), and so on). Throughout this lesson we’ll use SQLite’s core functions and call out where another dialect spells the same idea differently.

One important gotcha: SQLite’s UPPER() and LOWER() only fold ASCII letters (A-Z / a-z) by default. Accented or non-Latin characters pass through unchanged unless the ICU extension is loaded. Most production databases (MySQL, PostgreSQL, SQL Server) handle full Unicode case folding out of the box, so behavior can differ across systems – always test with your actual data.

Syntax

The general shape of a string function call is FUNCTION_NAME(argument, ...), used just like any other expression – inside SELECT, WHERE, ORDER BY, or nested inside another function call.

Function	Purpose	Example	Dialect notes
`LENGTH(str)`	Number of characters in a string	`LENGTH('Hello')` → 5	SQL Server uses `LEN()`; MySQL/PostgreSQL also support `CHAR_LENGTH()`
`UPPER(str)` / `LOWER(str)`	Convert case	`UPPER('abc')` → ‘ABC’	Same name in every major dialect
`SUBSTR(str, start, length)`	Extract a substring (1-based start position)	`SUBSTR('Hello', 2, 3)` → ‘ell’	SQLite also accepts `SUBSTRING()` as an alias; SQL Server only has `SUBSTRING()`
`TRIM(str)` / `LTRIM(str)` / `RTRIM(str)`	Remove leading/trailing characters (spaces by default)	`TRIM(' hi ')` → ‘hi’	Consistent across dialects
`REPLACE(str, find, repl)`	Substitute every occurrence of a substring	`REPLACE('a-b-c', '-', '_')` → ‘a_b_c’	Consistent across dialects
`INSTR(str, substr)`	1-based position of the first match, or 0 if not found	`INSTR('hello', 'll')` → 3	MySQL/PostgreSQL: `POSITION(sub IN str)` or `STRPOS()`; SQL Server: `CHARINDEX(sub, str)`
`str1 \|\| str2`	Concatenate two strings	`'a' \|\| 'b'` → ‘ab’	MySQL and SQL Server require `CONCAT()`; PostgreSQL supports both
`CONCAT(v1, v2, ...)` / `CONCAT_WS(sep, v1, ...)`	Concatenate, treating `NULL` as empty (and, for `CONCAT_WS`, skipping `NULL`s when inserting the separator)	see Examples below	Added to SQLite in version 3.44; also available in MySQL, PostgreSQL, and SQL Server

Examples

Example 1: Case conversion and length

This example builds a small products table and applies UPPER(), LOWER(), and LENGTH() to the product_name column.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT
);

INSERT INTO products (product_id, product_name, category) VALUES
  (1, 'Wireless Mouse', 'Electronics'),
  (2, 'usb-c Cable', 'Accessories'),
  (3, 'Mechanical Keyboard', 'Electronics');

SELECT
  product_name,
  UPPER(product_name) AS name_upper,
  LOWER(product_name) AS name_lower,
  LENGTH(product_name) AS name_length
FROM products
ORDER BY product_id;

Result:

product_name	name_upper	name_lower	name_length
Wireless Mouse	WIRELESS MOUSE	wireless mouse	14
usb-c Cable	USB-C CABLE	usb-c cable	11
Mechanical Keyboard	MECHANICAL KEYBOARD	mechanical keyboard	19

UPPER() and LOWER() return a brand-new string – they never modify the stored data. LENGTH() counts every character, including spaces and hyphens, which is why 'usb-c Cable' comes out to 11.

Example 2: Trimming whitespace and replacing text

Real-world data is rarely clean. This example shows a feedback table where some comments have accidental leading or trailing spaces, and demonstrates TRIM() alongside REPLACE().

CREATE TABLE feedback (
  id INTEGER PRIMARY KEY,
  raw_comment TEXT
);

INSERT INTO feedback (id, raw_comment) VALUES
  (1, '  Great product!  '),
  (2, 'Needs   improvement'),
  (3, '   Excellent!');

SELECT
  id,
  raw_comment,
  TRIM(raw_comment) AS trimmed,
  LENGTH(raw_comment) AS raw_len,
  LENGTH(TRIM(raw_comment)) AS trimmed_len,
  REPLACE(raw_comment, 'Needs', 'Requires') AS replaced
FROM feedback
ORDER BY id;

Result:

id	raw_comment	trimmed	raw_len	trimmed_len	replaced
1	‘ Great product! ‘	‘Great product!’	18	14	‘ Great product! ‘
2	‘Needs improvement’	‘Needs improvement’	19	19	‘Requires improvement’
3	‘ Excellent!’	‘Excellent!’	13	10	‘ Excellent!’

Notice row 2: TRIM() only removes characters from the start and end of the string, so the three internal spaces between ‘Needs’ and ‘improvement’ are untouched – trimmed_len equals raw_len. REPLACE() swaps every occurrence of the target substring, so it only changes row 2, where ‘Needs’ actually appears.

Example 3: Concatenation and substring extraction

This example joins first and last names into a full name with ||, then uses INSTR() together with SUBSTR() to split an email address into a username and a domain.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO staff (staff_id, first_name, last_name, email) VALUES
  (1, 'Diego', 'Ramirez', 'diego.ramirez@company.com'),
  (2, 'Mei', 'Chen', 'mei.chen@company.com'),
  (3, 'Sofia', 'Ivanova', 'sofia.ivanova@company.com');

SELECT
  first_name || ' ' || last_name AS full_name,
  email,
  SUBSTR(email, 1, INSTR(email, '@') - 1) AS username,
  SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM staff
ORDER BY staff_id;

Result:

full_name	email	username	domain
Diego Ramirez	diego.ramirez@company.com	diego.ramirez	company.com
Mei Chen	mei.chen@company.com	mei.chen	company.com
Sofia Ivanova	sofia.ivanova@company.com	sofia.ivanova	company.com

INSTR(email, '@') finds the 1-based position of the ‘@’ character. SUBSTR(email, 1, position - 1) then grabs everything before it, and SUBSTR(email, position + 1) (with no length argument) grabs everything from just after ‘@’ to the end of the string.

How It Works Step by Step / Under the Hood

A query’s logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. String functions most commonly live in the SELECT list, which means the engine has already identified and filtered the relevant rows (via FROM/WHERE/joins) before a single string function is ever called – the functions only run on rows that survived filtering, one call per row, per function.

Nested function calls, like SUBSTR(email, INSTR(email, '@') + 1) from Example 3, are evaluated inside-out for each row: first INSTR(email, '@') scans the row’s email value character by character to find the ‘@’ position, then that computed number is passed into SUBSTR() as the starting point. This happens independently for every row – there is no shared state between rows.

If a string function appears in WHERE instead – for example, WHERE UPPER(last_name) = 'SMITH' – the engine must call UPPER() on the last_name value of every row it examines before it can compare the result. A normal index on last_name can’t help here, because the index stores the original values, not their uppercased form; SQLite falls back to a full table scan unless you create a matching expression index (CREATE INDEX ... ON table(UPPER(last_name))) or use COLLATE NOCASE instead, which many engines can still use with a regular index.

Common Mistakes

Mistake 1: Using a function that doesn’t exist in SQLite

Developers coming from SQL Server often reach for LEN(). SQLite has no such function, so this fails outright:

CREATE TABLE items (item_id INTEGER PRIMARY KEY, item_name TEXT);
INSERT INTO items (item_id, item_name) VALUES (1, 'Notebook'), (2, 'Stapler');

SELECT item_name, LEN(item_name) FROM items;

This raises no such function: LEN. The portable, SQLite-supported equivalent is LENGTH():

CREATE TABLE items (item_id INTEGER PRIMARY KEY, item_name TEXT);
INSERT INTO items (item_id, item_name) VALUES (1, 'Notebook'), (2, 'Stapler');

SELECT item_name, LENGTH(item_name) AS name_length FROM items;

Result: Notebook → 8, Stapler → 7. Always check which function names your specific database dialect actually supports rather than assuming they’re universal.

Mistake 2: Assuming `||` skips NULL values

The || concatenation operator propagates NULL: if any operand is NULL, the whole expression becomes NULL. This surprises people who expect it to behave like CONCAT_WS().

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  middle_name TEXT,
  last_name TEXT
);

INSERT INTO people (id, first_name, middle_name, last_name) VALUES
  (1, 'John', NULL, 'Doe'),
  (2, 'Maria', 'Elena', 'Garcia');

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM people
ORDER BY id;

Result: row 1 (John, no middle name) returns full_name = NULL – not ‘John Doe’ as you might expect – while row 2 correctly returns ‘Maria Elena Garcia’. The fix is concat_ws(), which skips NULL arguments and avoids inserting an extra separator for them:

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  middle_name TEXT,
  last_name TEXT
);

INSERT INTO people (id, first_name, middle_name, last_name) VALUES
  (1, 'John', NULL, 'Doe'),
  (2, 'Maria', 'Elena', 'Garcia');

SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name
FROM people
ORDER BY id;

Result: row 1 → ‘John Doe’, row 2 → ‘Maria Elena Garcia’. Both rows now format correctly regardless of whether middle_name is present.

Best Practices

Use COALESCE() or concat_ws() instead of raw || whenever a concatenated column might contain NULL values.
TRIM() input data (emails, codes, names) before storing or comparing it, so stray whitespace doesn’t silently break equality checks and joins.
Avoid wrapping indexed columns in functions inside WHERE (e.g. WHERE UPPER(email) = ...) unless you’ve created a matching expression index – otherwise the engine can’t use a plain index and falls back to scanning every row.
Remember SQLite’s UPPER()/LOWER() are ASCII-only by default; test case handling with real accented or non-Latin data before relying on it in production.
Know your dialect’s function names before you write cross-database SQL: SUBSTR vs SUBSTRING, LENGTH vs LEN, || vs CONCAT(), INSTR vs CHARINDEX/POSITION.
Prefer REPLACE() over multiple chained string manipulations when you just need a straightforward find-and-replace across a whole column.

Practice Exercises

Using a products table with columns product_name and price, write a query that returns each product_name converted to uppercase along with its character length.
Given a table storing phone numbers as text with inconsistent formatting like ' 555-0142 ', write a query that both trims the whitespace and replaces the hyphen with nothing, producing a clean digit string like '5550142'.
Given a table with first_name and a nullable last_name column, write a query that safely concatenates them into one display_name column, so that rows with a missing last_name still show just the first name instead of NULL.

Summary

String functions are scalar functions – one input row in, one output value out – and can appear in SELECT, WHERE, ORDER BY, and more.
Core SQLite functions covered: LENGTH(), UPPER()/LOWER(), SUBSTR(), TRIM()/LTRIM()/RTRIM(), REPLACE(), INSTR(), ||, and CONCAT()/CONCAT_WS().
TRIM() only strips leading/trailing characters; it never touches whitespace in the middle of a string.
|| propagates NULL – any NULL operand makes the whole result NULL; use concat_ws() or COALESCE() to avoid that trap.
Wrapping an indexed column in a function inside WHERE typically prevents normal index use, forcing a full scan.
Function names vary across SQL dialects – always confirm the equivalent function name for your specific database engine.

SQL String Functions

Overview / How String Functions Work

Syntax

Examples

Example 1: Case conversion and length

Example 2: Trimming whitespace and replacing text

Example 3: Concatenation and substring extraction

How It Works Step by Step / Under the Hood

Common Mistakes

Mistake 1: Using a function that doesn’t exist in SQLite

Mistake 2: Assuming || skips NULL values

Best Practices

Practice Exercises

Summary

Table of Contents

Mistake 2: Assuming `||` skips NULL values