Here we will learn what is SQLite Substr() function and how to use SQLite Substr() function with WHERE clause to get characters from a given string starting at a specified position and length.
In SQLite substr() function is used to extracts the substring from a specified position of string based on the length.
Following is the syntax of SQLite Substr() to get the characters from a string starting from the specified position with predefined length.
In above SQLite substr() function syntax we defined multiple parameters those are
string – Its source string which used to extract substring.
start_position – The start_position is an integer value and it indicates a starting position to extract the characters from a string.
If start_position value positive means then the string extraction starts from the position which is defined.
If start_position value negative means then the string extraction starts from the end of the string and counts from backward.
length – Its an integer value that indicates a number of characters to extract from string and it’s Optional.
If we define length value means then substr() function will extract the defined length of characters from a string starting from the position which we defined.
If we omit this length parameter means then substr() function will return all the characters from string starting from the position which we defined.
Now we will see how to use the SQLite substr() function to extract defined number characters from string with examples.
Following is the SQLite substr() function query to get characters from string based on the defined starting position.
sqlite> SELECT substr('SQLlite',4);
substr('SQLlite',4)
-------------------
lite
If you observe the above example we defined starting position as “4” so substr() function will start extracting the characters from the “4th” position to till the end of string because we didn’t define any specific length.
Now we will see the example how substr() function will return substring if we define starting position as a negative value.
Following is the query to extract substring values from string with negative starting position.
sqlite> SELECT substr('SQLlite',-2);
substr('SQLlite',-2)
--------------------
te
If you observe the above result substr() function return characters from the end of the string because we defined starting position with a negative value.
Now we will see another example how substr() function will return characters from a string if we defined both starting position and length. Following is the query to retrieve characters from string based on starting position and length.
sqlite> SELECT substr('SQLlite',2,4);
substr('SQLlite',2,4)
--------------------
QLli
If you observe above result substr() function return characters starting from position “2” till “4” characters based on the defined length.
Now we will see how to use substr() function in SQLite to get a substring from string with examples for that we need to create two tables called dept_master and emp_master using the following query.
To create and insert some data in the dept_master table execute the following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control'),
('Marketing');
Once we create and insert data execute the following query to see the data of the dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute the following queries to create and insert some data in the emp_master table.
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 get the substring from “first_name” column from the emp_master table.
sqlite> SELECT first_name,substr(first_name,3,4)
FROM emp_master
WHERE dept_id = 3;
first_name substr(first_name,3,4)
---------- ----------------------
Vinay nay
Shweta weta
Khyati yati
If you observe the above result SQLite substr() function returns “4” characters from the first_name column starting from the “3rd” position.
By using WHERE clause in SQLite substr() function we can get a substring from a string based on the particular expression.
Following is the SQLite substr() function query to get substring from first_name column in emp_master table where dept_id = 1.
sqlite> SELECT first_name,substr(first_name,3)
FROM emp_master
WHERE dept_id = 1;
first_name substr(first_name,3)
---------- --------------------
Honey ney
Sonal nal
If you observe above result SQLite substr() function return first_name values starting from the “3rd” position.
Now we will see how SQLite substr() function will return substring values if we defined negative value for starting position. Following is the SQLite substr() function query to get substring values with a negative starting position.
sqlite> SELECT first_name,substr(first_name,-4)
FROM emp_master
WHERE dept_id = 2;
first_name substr(first_name,-4)
---------- ---------------------
Shweta weta
Jagruti ruti
Yamini mini
Shwets wets
If you observe above result we got the employee details whose department id equals to “2” and first_name column with “4” characters from the end of the string because we defined starting position value as negative.
Now we will see how to use SQLite substr() function with negative position and length. Following is the SQLite substr() function query to get first_name column string values based on defined position and length.
sqlite> SELECT first_name, SUBSTR(first_name ,-6,3)
FROM emp_master
WHERE length(first_name) > 5;
first_name SUBSTR(first_name ,-6,3)
---------- ------------------------
Shweta Shw
Jagruti agr
Shweta Shw
Yamini Yam
Khyati Khy
Shwets Shw
If you observe the above result we got employees first_name column values with defined negative position and length whose first_name length greater than 5.
This is how we can use the SQLite substr() function to get a substring from a string based on our requirements based on a specified position and length.