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