SQLite SUBSTR function

The SQLite SUBSTR function is used to extract a substring from a given string. This function takes three arguments: the original string, the starting index position of the substring, and the length of the substring to be extracted.

Syntax

The syntax of the SQLite SUBSTR function is as follows:

SUBSTR(original_string, start_index, length)

Where:

original_string is the string from which the substring needs to be extracted.
start_index is the starting index position of the substring.
length is the length of the substring to be extracted.

Example

For example, suppose we have the following table named “students” in our SQLite database:

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

And we want to extract the first three characters of the name of all students whose name starts with the letter “J”. We can use the SQLite SUBSTR function in the SELECT statement like this:

SELECT id, name, SUBSTR(name,1,3) as short_name
FROM students
WHERE name LIKE 'J%';

This will return a result set that includes the ID, name, and the first three characters of the name of all students whose name starts with the letter “J”.

In summary, the SQLite SUBSTR function is a very useful function for extracting substrings from a given string. It takes three arguments: the original string, the starting index position of the substring, and the length of the substring to be extracted.