Here we will learn what is SQLite Trim() function and how to use SQLite Trim() function to remove specified characters from both leading and trailing ends of string.
Generally in SQLite Trim() function is used to remove specified characters from both starting and ending of the string.
In case if we didn’t specify any characters to remove for Trim() function, then it will remove only space characters from both ends of a string.
Following is the syntax of SQLite Trim() function to remove leading and trailing characters from a string.
In above SQLite trim() function syntax, we defined multiple parameters those are
string – Its source string which used to trim characters.
trim_character – The trim_character is a character that we need to remove from both ends of the string. It’s Optional.
In case we didn’t mentioned trim_character then trim() function will remove the spaces from the starting and ending of the source string.
Now we will see how to use the SQLite trim() function to remove the starting and ending of characters with examples.
Following is the SQLite trim() example to remove leading and trailing spaces of string.
sqlite> SELECT TRIM(' Welcome to Tutlane.com ');
TRIM(' Welcome to Tutlane.com ')
-----------------------------------
Welcome to Tutlane.com
If you observe the above example we didn’t specify any trim character that’s the reason trim() function removed starting and ending spaces of string.
Following is the SQLite trim() example with trim character to remove the starting and ending of characters from a string.
sqlite> SELECT TRIM('##Welcome to Tutlane.com####','#');
TRIM('##Welcome to Tutlane.com####')
-----------------------------------
Welcome to Tutlane.com
If you observe above SQLite trim() function example we defined trim character “#” to remove from the string. So it removed special character “#” from both sides of the string.
Following is another SQLite Trim() function statement to remove leading and trailing character(s) 'ma' from the given string 'madam'.
sqlite> SELECT 'madam', trim('madam','ma');
'madam' trim('madam','ma')
---------- ------------------
madam d
If you observe the above SQLite Trim() function statement we are removing starting and ending characters “ma” from string “madam”.
Following is another example of SQLite Trim() function to remove characters “123” from string “123321tutlane321123”.
sqlite> SELECT TRIM('123321tutlane.com321123','123');
TRIM('123321tutlane.com321123')
-----------------------------------
tutlane.com
If you observe the above result Trim() function removed starting and ending characters “123” from the given string.
Now we will see how to use Trim() function in SQLite tables with examples for that we need to create a table called emp_master using the following query.
To create and insert some data in the emp_master table execute the following query.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel', 10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 50000,3),
(9,'Shwets','Jariwala',19400,2);
Now run the following query to check the records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
Now write the query like as shown following to remove starting and ending characters “sh” from the first name of employees whose first name starts with “sh” from the emp_master table.
sqlite> SELECT trim(first_name,'Sh')
FROM emp_master
WHERE first_name LIKE 'Sh%';
trim(first_name,'Sh')
---------------------
weta
weta
wets
If you observe the above result the Trim() function removed leading and trailing characters “sh” from the first_name column in emp_master table.
This is how we can use SQLite Trim() function to remove starting and ending characters from string based on our requirements.