The SQLite trim() function trims whitespace or other characters from both sides of a string.

The function returns a copy of the string that you provide as an argument, with either whitespace, or other characters as specified, trimmed from both sides.

Syntax

You can call trim() with either one or two arguments.

trim(X)
trim(X,Y)

• If you provide one argument, this is the string to trim. In this case, whitespace is trimmed (if any) from both sides of the string.
• If you provide two arguments, the second argument contains the characters that are to be removed from both sides of the string.

Trim Whitespace

Here’s an example to demonstrate how to trim whitespace from a string.

.mode quote
SELECT trim(' Red Planet ');

Result:

'Red Planet'

As you can see, the middle space remains intact. Only the left and right spaces are trimmed.

In the first line I’m simply setting the mode, and this is completely unrelated to the trim() function.

I set the mode to “quote” in order to make it easier to see the effect of the example.

Here it is without the trim() function:

SELECT ' Red Planet ';

Result:

' Red Planet '    

In this case, both spaces remain intact.

Multiple Spaces

If there are multiple spaces trim() trims all of them.

SELECT trim('    Red Planet    ');

Result:

'Red Planet'     

Trim Other Characters

The trim() function accepts an optional second argument that allows you to specify which character/s to trim from the string.

Here’s an example of using that syntax.

SELECT trim('---Red---', '-');

Result:

'Red'          

Below is another example. This time I specify more than one character to trim.

SELECT trim('*--Red--*', '-*');

Result:

'Red'          

Notice that the characters don’t need to be in the same order. In my example, I specified -* and this affected both -* and *-.

Case-Sensitivity

Be mindful of case-sensitivity. Here’s what happens if I use a different case for one of the characters.

SELECT trim('Big Toe', ' toe');

Result:

'Big T'     

So if I wanted to eliminate the second word in this example, I would need to use an uppercase T.

SELECT trim('Big Toe', ' Toe');

Result:

'Big'     

Bear in mind that this will also remove other words/strings that aren’t necessarily the full word Toe.

Here are some examples:

.mode column
SELECT 
  trim('Big SaToe', ' Toe') AS Satoe,
  trim('Big oTe', ' Toe') AS oTe,
  trim('Big Teo', ' Toe') AS Teo;

Result:

Satoe       oTe         Teo       
----------  ----------  ----------
Big Sa      Big         Big       

Trim Just the Left or Right Part of the String

You can also use ltrim() to trim just the left part of the string, and rtrim() to trim just the right part.