SQLite trim() Function

The SQLite trim() function removes the longest string containing all the characters specified by the parameter (whitespace by default) from the start and end of a string.

You can also delete specified characters from the beginning of a string using ltrim(), or delete from the end of a string using rtrim().

Syntax

This is the syntax of the SQLite trim() function:

trim(str)

or

trim(str, characters)

Parameters

str

Required. a string.

characters

Optional. A string containing the characters to remove. The default is a space.

Return value

The SQLite trim() function removes the longest string containing all the characters specified by the parameter (one space by default) from the beginning and end of a gaven string, and returns the character-removed string.

Examples

This example shows the basic usage of the SQLite trim() function:

SELECT
    length(trim('a  ')),
    length(trim('  a')),
    length(trim(' a '));

length(trim('a  '))  length(trim('  a'))  length(trim(' a '))
-------------------  -------------------  -------------------
1                    1                    1

here,

• We’ve only used one parameter, so trim() removed spaces from both ends of the string.
• To make the result look more intuitive, we use length() function to display the length of the trimed string.

Let’s use trim() again to remove specified characters from both ends of a string:

SELECT trim('xxyHELLOzxy', 'xyz');

trim('xxyHELLOzxy', 'xyz')
--------------------------
HELLO

Here, since we specified the characters xyz to be deleted, xxy in both ends of xxyHELLOzxy are deleted.zxy

Try the SQLite example online

Run and edit the SQL examples from this page in the SQLite Playground. It uses a real SQLite database in your browser, so you can test the query without installing SQLite.