How to Trim a String in SQLite

Problem:

You’d like to remove spaces or a specific characters from the beginning and end of a string in SQLite.

Example:

Our database has a table named category with data in two columns: id and name.

Let’s trim the name of each category of products to remove the unnecessary space at the beginning and end.

Solution:

We’ll use the TRIM function. Here’s the query you would write:

SELECT TRIM(' ', name) AS new_name
FROM category;

Note that the character to remove is optional; you can just specify the name of the column if you want to remove the space character:

SELECT TRIM(name) AS new_name
FROM category;

Here’s the result of both queries:

Discussion:

Use the TRIM function in an SQLite database if you want to trim a string in a table. This function allows you to remove a specific character from the beginning and/or end of a string. It takes the following arguments:

• The character you want to trim from the string, which is a space by default.
• The name of the string column to be trimmed.

In our example, that looks like:

TRIM(' ' , name)

SQLite also allows us to remove a space or other characters from only the beginning or end of a string.

The example below removes the space from the end of each category name using the RTRIM() function:

SELECT RTRIM(name) AS new_name
FROM company;

Like TRIM(), the RTRIM() function takes two arguments: the characters to remove, and the string to be trimmed. Again, you can just specify the name of the column if the character to trim is a space.

If you would like to remove characters from the beginning of a string but not from the end, use the LTRIM() function:

SELECT LTRIM(name) AS new_name
FROM company;

Of course, you can use this function with two arguments: the characters to remove and the string to be trimmed.

The query above returns the name column without a space at the beginning: