SQLite | SQLite Functions | Removing Characters from the Start and End of a String (trim, rtrim, and ltrim Functions)

Tags:

• SQLite
• Database

The trim function removes spaces from the start and end of a string. The rtrim and ltrim functions work similarly. This article explains how to use these functions.

Using the trim Function

The trim function removes specified characters from both ends of a string. Its syntax is as follows.

trim(string)
trim(string, characters)

With one argument, trim removes consecutive spaces from the beginning and end of the string.

Note
Spaces at the beginning and end are removed, but spaces within the string are preserved.

When a column is supplied, the function returns each stored value without leading or trailing spaces. If a second argument is supplied, those characters are removed from both ends instead of spaces.

The related rtrim function removes specified characters from the end of a string, while ltrim removes them from the beginning.

rtrim(string)
rtrim(string, characters)

ltrim(string)
ltrim(string, characters)

–

Create a table for the example.

create table msgdb (id integer, msg text);

sqlite> create table msgdb (id integer, msg text);
sqlite> 

Insert the following data.

insert into msgdb values(1, ' Hello! ');
insert into msgdb values(2, ' Good Bye ');
insert into msgdb values(3, '__Thank you__');

sqlite> insert into msgdb values(1, ' Hello! ');
sqlite> insert into msgdb values(2, ' Good Bye ');
sqlite> insert into msgdb values(3, '__Thank you__');
sqlite> 

Use trim, ltrim, and rtrim to remove spaces from the values in the msg column.

select id, quote(trim(msg)), quote(ltrim(msg)), quote(rtrim(msg)) from msgdb;

sqlite> select id, quote(trim(msg)), quote(ltrim(msg)), quote(rtrim(msg)) from msgdb;
id          quote(trim(msg))  quote(ltrim(msg))  quote(rtrim(msg))
----------  ----------------  -----------------  -----------------
1           'Hello!'          'Hello! '          ' Hello!'        
2           'Good Bye'        'Good Bye '        ' Good Bye'      
3           '__Thank you__'   '__Thank you__'    '__Thank you__'  
sqlite> 

The quote function surrounds the returned values with single quotes so the result is easier to inspect. The output shows that spaces were removed from the appropriate ends. See Surrounding a String with Single Quotes (quote Function) for details.

Next, pass an underscore (_) as the second argument to remove underscores from the appropriate ends of each value.

select id, trim(msg, '_'), ltrim(msg, '_'), rtrim(msg, '_') from msgdb;

sqlite> select id, trim(msg, '_'), ltrim(msg, '_'), rtrim(msg, '_') from msgdb; 
id          trim(msg, '_')  ltrim(msg, '_')  rtrim(msg, '_')
----------  --------------  ---------------  ---------------
1            Hello!          Hello!           Hello!        
2            Good Bye        Good Bye         Good Bye      
3           Thank you       Thank you__      __Thank you    
sqlite> 

For the final row, trim removes underscores from both ends, ltrim from the beginning, and rtrim from the end.