SQLite String Functions: SUBSTR, REPLACE, INSTR, and More

Strings Are Where Most Real Queries Live

Numbers are easy. Strings are where queries get messy — names with stray whitespace, emails in mixed case, IDs glued together with dashes, free-text fields that almost-but-not-quite match. SQLite ships a small, focused set of string functions that handle most of this without needing application code.

This page walks through the ones you'll reach for first: concatenation, slicing, searching, replacing, trimming, and formatting.

Concatenation Uses ||, Not CONCAT

SQLite doesn't have a CONCAT function. Strings are joined with the || operator:

SELECT 'Hello, ' || 'world!' AS greeting;

SELECT 'User #' || 42 || ' joined' AS message;

Numbers and other types get coerced to text automatically. The catch: if any operand is NULL, the whole expression becomes NULL. That's standard SQL behavior, but it surprises people:

SELECT 'Hello, ' || NULL || '!' AS greeting;

SELECT 'Hello, ' || COALESCE(NULL, 'friend') || '!' AS safe_greeting;

Wrap nullable columns in COALESCE(col, '') or COALESCE(col, 'default') when you want a missing value to not nuke the entire string.

Length, Upper, Lower

The three you'll use constantly:

SELECT
    LENGTH('résumé')        AS char_count,
    UPPER('hello world')    AS upper_case,
    LOWER('HELLO WORLD')    AS lower_case;

LENGTH returns the number of characters for text, not bytes. If you actually want bytes (rare, but useful for storage analysis), use OCTET_LENGTH. UPPER and LOWER only transform ASCII letters by default — accented characters pass through unchanged unless you've loaded the ICU extension.

SUBSTR: Slicing Strings

SUBSTR(text, start, length) extracts a piece of a string. Indexes are 1-based — 1 is the first character, not 0:

SELECT
    SUBSTR('codecademy', 1, 4)  AS first_four,
    SUBSTR('codecademy', 5)     AS rest,
    SUBSTR('codecademy', -3)    AS last_three;

A few things to remember:

• The third argument is optional. Without it, you get everything from start to the end.
• A negative start counts from the end of the string.
• If start is past the end, you get an empty string, not an error.

SUBSTRING is accepted as a synonym, in case your muscle memory comes from another database.

INSTR: Finding a Substring

INSTR(haystack, needle) returns the 1-based position of the first occurrence of needle in haystack, or 0 if it's not found:

SELECT
    INSTR('user@example.com', '@')        AS at_position,
    INSTR('user@example.com', 'xyz')      AS not_found,
    SUBSTR('user@example.com',
           1,
           INSTR('user@example.com', '@') - 1) AS local_part;

That last expression is the SQLite idiom for "everything before the @": find the delimiter with INSTR, then slice with SUBSTR. You'll write this combination often. Notice that INSTR returning 0 when nothing matches means you should check before slicing — feeding 0 into SUBSTR quietly gives weird results.

REPLACE: Swapping One Substring for Another

REPLACE(text, old, new) replaces every occurrence of old with new:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Hello, world. Hello, again.',
            'Hello',
            'Hi')                                   AS shortened,
    REPLACE('  spaced  out  ', ' ', '')             AS no_spaces;

It's case-sensitive and doesn't take a regex — just a literal substring. For more complex transformations you can chain REPLACE calls, but past two or three nested ones it's time to do the work in the application instead.

TRIM, LTRIM, RTRIM

User-entered data tends to come with whitespace on the ends. TRIM strips it:

SELECT
    TRIM('   hello   ')              AS both_sides,
    LTRIM('   hello   ')             AS left_only,
    RTRIM('   hello   ')             AS right_only,
    TRIM('---hello---', '-')         AS custom_chars;

By default they remove spaces. Pass a second argument to specify which characters to strip — every character in the second argument is treated as a member of a "set to remove," not as a literal substring. So TRIM('xxxhelloxx', 'x') gives 'hello'.

printf: Formatting Numbers and Strings

When you need a formatted string — fixed decimal places, padded numbers, hex output — printf (also spelled format) does it:

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s is %d',     'Ada',  30)         AS sentence;

The format specifiers follow C conventions, which means %d, %s, %f, %x, padding with 0 or spaces, and so on. This is much cleaner than building strings with || and a pile of CASTs.

LIKE vs GLOB: Pattern Matching

Two operators, two different worlds.

LIKE uses SQL's classic wildcards — % for any sequence of characters, _ for a single character — and is case-insensitive for ASCII:

SELECT 'Alice'  LIKE 'a%'      AS alice_match;
SELECT 'README' LIKE '_____'   AS five_chars;
SELECT 'foo.txt' LIKE '%.txt'  AS is_text_file;

GLOB uses Unix shell wildcards — * for any sequence, ? for a single character, [abc] for character classes — and is case-sensitive:

SELECT 'Alice'  GLOB 'A*'         AS alice_match;
SELECT 'Alice'  GLOB 'a*'         AS lowercase_match;
SELECT 'log7'   GLOB 'log[0-9]'   AS digit_class;

The picking-between-them rule: LIKE for human-style "starts with," "contains," "ends with" matching. GLOB when case sensitivity matters or you need character classes. Both can use indexes, but only when the pattern is anchored to the start ('foo%', not '%foo') — leading wildcards force a full scan.

Splitting Strings: There's No SPLIT

SQLite doesn't ship a SPLIT_STRING function. The two practical workarounds:

-- Pull a single piece using INSTR + SUBSTR
WITH addresses AS (
    SELECT 'rosa@example.com'   AS email UNION ALL
    SELECT 'boris@coddy.tech'   UNION ALL
    SELECT 'ada@lovelace.org'
)
SELECT
    SUBSTR(email, 1, INSTR(email, '@') - 1)  AS local_part,
    SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM addresses;

For splitting on a delimiter into multiple rows, the cleanest path is json_each over a JSON array, or a recursive CTE. We'll cover both in later chapters — for now, just know that "give me every word" isn't a one-liner in SQLite.

A Worked Example: Cleaning Up Names

Putting it together. Imagine a users table with messy display names — extra whitespace, mixed case, optional titles like "Dr. " or "Mr. " you want stripped:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Dr. Ada Lovelace  '),
      (2, 'mr. Boris Karloff'),
      (3, 'Rosa  Parks'),
      (4, '   alan TURING')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'dr. ', ''),
        'mr. ', ''
      )
    ) AS cleaned
FROM users;

The expression reads inside-out: strip outer whitespace, lowercase, drop the titles, trim again in case removing the title left a leading space. Each step is a single function — the complexity comes from stacking them. When the stack grows past three or four levels, that's a hint to either use a generated column (Chapter: Advanced Features) or do the cleaning during data import.

What You Take Away

• || for concatenation; NULL poisons the result, so use COALESCE.
• SUBSTR and INSTR together cover most "find and slice" needs.
• REPLACE swaps every occurrence of a literal substring.
• TRIM and friends accept a custom character set, not just whitespace.
• printf is the right tool for formatted output.
• LIKE for case-insensitive SQL wildcards, GLOB for case-sensitive shell-style patterns.

Next: Numeric Functions

Strings handled, the obvious next stop is numbers — rounding, absolute values, division quirks, and the math functions SQLite added in recent versions. That's the next page.