Menu

SQLite replace() Function

Updated on

SQLite replace(X,Y,Z) returns a copy of X with every occurrence of Y replaced by Z. It uses the BINARY collating sequence to find matches, so matching is case-sensitive even when the source column uses a different collation. If Y is an empty string, SQLite returns X unchanged. If Z is not already a string, SQLite casts it to UTF-8 text before processing.

Syntax

replace(X, Y, Z)
  • X: input string.
  • Y: substring to find.
  • Z: replacement value; non-string values are converted to UTF-8 text.

Case-sensitive matching and empty search strings

The BINARY matching rule distinguishes uppercase from lowercase characters. An empty Y does not insert Z between characters; it leaves X unchanged:

SELECT replace('Banana', 'a', 'o') AS lowercase_match,
       replace('Banana', 'A', 'o') AS uppercase_no_match,
       replace('banana', '', 'X') AS empty_search;

lowercase_match | uppercase_no_match | empty_search
----------------|--------------------|-------------
Bonono          | Banana             | banana

To remove a substring, use an empty replacement value:

SELECT '[' || replace('Hello World', 'World', '') || ']' AS removed_word;

removed_word
------------
[Hello ]

Further reading