How to Trim a String in SQLite
Database:
Operators:
Table of Contents
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
.
id | name |
---|---|
1 | ' sweets ' |
2 | 'meat ' |
3 | ' vegetables' |
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:
new_name |
---|
'sweets' |
'meat' |
'vegetables' |
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; |
new_name |
---|
' sweets' |
'meat' |
' vegetables' |
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:
new_name |
---|
'sweets ' |
'meat ' |
'vegetables' |