SQLite Substr() Function

  Total Views : 373
  Comments : 0
  Keywords : SQLite substr() to get number of characters from a particular position of given string with example, SQLite get substring using substr() function with example

Here we will learn what is SQLite Substr() function and how to use SQLite Substr() function with WHERE clause to get characters from given string starting at a specified position and length. 

SQLite Substr() Function

In SQLite substr() function is used to extracts the substring from a specified position of string based on the length.

Syntax of SQLite Substr()

Following is the syntax of SQLite Substr() to get the characters from a string starting from the specified position with predefined length.

 

substr( string, start_position, 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 starting position to extract the characters from 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 string and counts from backward.

 

length – Its an integer value which indicates that 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 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.

SQLite Substr() Function Examples

Now we will see how to use 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.

 

sqliteSELECT substr('SQLlite',4);

 

substr('SQLlite',4)

-------------------

lite

If you observe above example we defined starting position as “4” so substr() function will start extracting the characters from “4th” position to till end of string because we didn’t defined any specific length.

 

Now we will see the example how substr() function will return substring if we define starting position as negative value.

 

Following is the query to extract substring values from string with negative starting position.

 

sqliteSELECT substr('SQLlite',-2);

 

substr('SQLlite',-2)

--------------------

te

If you observe above result substr() function return characters from end of the string because we defined starting position with negative value.

 

Now we will see another example how substr() function will return characters from string if we defined both starting position and length. Following is the query to retrieve characters from string based on starting position and length.

 

sqliteSELECT 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.

SQLite Substr() Function with Tables

Now we will see how to use substr() function in SQLite to get substring from string with examples for that we need to create two tables called dept_master and emp_master using following query.

 

To create and insert some data in dept_master table execute 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 following query to see the data of dept_master table.

 

sqliteSELECT FROM dep_master;

 

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute following queries to create and insert some data in 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.

 

sqliteSELECT 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 emp_master table.

 

sqliteSELECT 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 above result SQLite substr() function returns “4” characters from first_name column starting from “3rd” position.

SQLite substr() Function with Where Clause

By using WHERE clause in SQLite substr() function we can get substring from string based on particular expression.

 

Following is the SQLite substr() function query to get substring from first_name column in emp_master table where dept_id = 1.

 

sqliteSELECT 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 “3rd” position.

SQLite Substr() Function with Negative 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 negative starting position.

 

sqliteSELECT 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 end of the string because we defined starting position value as negative.

SQLite Substr() Function with Negative Position and Length

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.

 

sqliteSELECT 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 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 SQLite substr() function to get substring from string based on our requirements based on specified position and length. 

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User