SQLite String Functions: Search, Replace, and Trim

Finding or changing text in a SQLite query can call for different functions. SQLite string functions are built-in SQL functions that take input values and return results for a query.

I’ll explain how to choose functions for searching and replacing text, including how trim() removes characters from string ends.

TL;DR

Use instr() to find text, replace() to substitute literal text, trim() to remove characters from either end, and length() to measure a value. Built-in upper() and lower() change ASCII letters only.

• instr(X,Y) returns the 1-based position of Y in X, or 0 when it is absent.
• replace(X,Y,Z) replaces every occurrence of Y with Z.
• trim(X,Y), ltrim(X,Y), and rtrim(X,Y) remove any characters from Y at the specified ends.
• length(text) counts Unicode codepoints before the first NUL. length(BLOB) counts bytes.

What Is a SQLite String Function?

SQLite string functions are built-in SQL functions that take values and return text, a number, or NULL. They can appear in a SELECT expression or wherever the SQL grammar accepts an expression, such as a WHERE condition.

The built-in set handles common operations, while separate pages cover extracting part of a string with substr() and joining text values. I keep those tasks separate here so the function behavior stays clear.

How to Use SQLite String Functions

Each function takes its input in parentheses, and the SELECT list gives the result a name. The examples below use literals so you can see the return value before applying the same expression to a table column.

SQLite also accepts expressions in a WHERE clause. For example, instr(email, ‘@’) returns a positive position when the address contains an at sign. A missing match returns zero, so compare the result with zero rather than treating it as a Boolean string result.

Step 1: Search and replace text

instr() searches its first argument for the second. Its result starts at 1, which makes it useful for checking whether a delimiter occurs before using its position in another expression.

SELECT instr('SQLite string functions','string') AS instr_position,
       instr('SQLite','x') AS instr_missing,
       replace('a-b-a','a','X') AS replaced;

The tested output is 8 | 0 | X-b-X. A missing search returns zero, and replace() changes both matching letters because it replaces every occurrence.

Both functions return NULL if an input argument is NULL. replace() searches for literal text and an empty search string leaves the original value unchanged. Use instr() when you need a position, then handle zero explicitly if later logic depends on a match.

Step 2: Trim characters from string ends

With one argument, trim(), ltrim(), and rtrim() remove spaces from both ends, the left end, or the right end. With a second argument, each function removes any character listed there as a set.

SELECT trim('xytextyx','xy') AS trimmed,
       ltrim('  text') AS left_trimmed,
       rtrim('text  ') AS right_trimmed;

The output is text | text | text. The trim set “xy” removes repeated x or y characters at either end. It does not remove the sequence “xy” as a unit.

For example, trim(‘abtextba’,’ab’) removes any run of a or b from both ends. It does not touch matching letters in the middle. Use ltrim() or rtrim() when the unwanted characters can occur on only one side, which makes the cleanup rule explicit.

Step 3: Count text and bytes

length() counts Unicode codepoints for TEXT and bytes for a BLOB. octet_length() reports the byte length of text in the database encoding. I compare the same word as text and as a BLOB to show how the storage type affects the measure.

SELECT length('café') AS text_codepoints,
       length(CAST('café' AS BLOB)) AS blob_bytes,
       octet_length('café') AS utf8_bytes;

On the tested UTF-8 SQLite build, the results are 4 | 5 | 5. The accented é is one codepoint and two UTF-8 bytes. A text value containing U+0000 is a special case because length() stops at the first NUL.

Step 4: Change letter case

upper() converts lowercase ASCII letters, and lower() converts uppercase ASCII letters. This is the documented built-in behavior, so test the exact character range your application stores rather than assuming locale-aware conversion.

SELECT upper('sqlite café') AS upper_ascii,
       lower('SQLITE CAFÉ') AS lower_ascii;

The result is SQLITE CAFé | sqlite cafÉ. Only ASCII letters change case. SQLite’s documentation points to the ICU extension for Unicode-aware case conversion, so confirm that your application library includes and loads it.

String Function Limits and Edge Cases

Choose the function based on the value type and the operation, not on a name borrowed from another database. SQLite’s core function reference at SQLite built-in functions is the broader index. The official core function documentation defines exact behavior.

Case	Behavior	What to check
instr() finds no match	Returns 0	Check for zero before using the result as a position
trim(X,Y)	Removes any characters in Y from the ends	Pass the set of characters you want removed
length(TEXT)	Counts codepoints before the first NUL	Use octet_length() for encoded byte size
length(BLOB)	Counts bytes	Confirm the expression is a BLOB if bytes are intended
upper() and lower()	Convert ASCII letters by default	Use ICU or application code for Unicode case handling

SQLite forum users ask why “upper and lower dont work on sqlite” for Unicode letters. The built-ins convert ASCII and leave non-ASCII case conversion to an extension or application code. Apply that limit to comparisons, searches, or normalization rules for non-English text.

Collations affect comparisons and sorting, not the output of upper() or lower(). If the goal is a Unicode-aware search, decide where case folding belongs in the application and keep that choice consistent for stored values and queries.

SQLite covers nearby text tasks with different features. For slicing, substr() starts at character 1 and accepts a negative start to count from the right. The SQLite substring guide covers those position rules and length arguments.

For joining values, SQLite uses the concatenation operator and provides concat() in current releases. See the SQLite concatenation guide for NULL behavior and examples. SQLite has no built-in split() function in its core scalar function list, so splitting delimited values needs a different approach, such as a recursive query or an extension.

Keep these operations separate when the query has more than one text task. A function that trims a delimiter, a function that locates it, and a function that extracts the surrounding text each has a distinct return value and edge behavior.

Conclusion: Match the Function to the Text Task

Use instr() and replace() for finding and changing literal text, trim variants for end characters, and length() or octet_length() according to whether you need codepoints or bytes. Treat upper() and lower() as ASCII operations unless your SQLite build adds Unicode support.

FAQ

These answers cover the behavior that tends to change a query’s result.