Here we will learn what is Length() function in SQLite and how to use SQLite Length() function to return a number of characters in a defined string with examples.
Generally, in SQLite Length() function is used to return length of the string or number of characters in the string.
The Length() function will always return an integer value indicating the length of the defined string parameter. In case, if a defined string value is NULL means then it will return NULL.
Following is the syntax of SQLite Length() function to a total number of characters in the string.
In above SQLite length() function syntax, we defined parameters those are
string – Its source string which is used to calculate the number of characters in the string.
Now we will see how to use SQLite Length() function to return a number of characters in a string with examples.
Following is the simple example of SQLite Length() function to get a number of characters in the defined string.
sqlite> SELECT length('Tutlane.com');
length('Tutlane.com')
----------------
11
If you observe above SQLite Length() example it returns length or number of characters in the string “Tutlane.com”.
We will see some other simple examples of SQLite Length() function.
sqlite> SELECT length('');
length('')
----------------
0
sqlite> SELECT length(' ');
length(' ')
----------------
2
sqlite> SELECT length(1000);
length(1000)
----------------
4
sqlite> SELECT length(NULL);
length(1000)
----------------
NULL
Now we will see how to use Length() 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 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 get length of first_name column in emp_master table.
sqlite> SELECT emp_id,first_name,length(first_name) FROM emp_master;
emp_id first_name length(first_name)
---------- ---------- ------------------
1 Honey 5
2 Shweta 6
3 Vinay 5
4 Jagruti 7
5 Shweta 6
6 Sonal 5
7 Yamini 6
8 Khyati 6
9 Shwets 6
If you observe above result we got number of characters in first_name column in the emp_master table.
Now we will write another SQLite query to get the length of first_name column in emp_master table whose length of string characters greater than 5.
sqlite> SELECT emp_id,first_name,length(first_name) FROM emp_master WHERE length(first_name) > 5;
emp_id first_name length(first_name)
---------- ---------- ------------------
2 Shweta 6
4 Jagruti 7
5 Shweta 6
7 Yamini 6
8 Khyati 6
9 Shwets 6
If you observe above result we got the length first_name column in emp_master whose length of string characters greater than 5.
This is how we can use SQLite Length() function to get number of characters in defined string based on our requirements.