SUBSTRING Function in SQL

The SUBSTRING string function in Structured Query Language shows the characters or sub-string from the specific index value of the original string. We also use the SUBSTRING function with the SQL tables.

Syntax of SUBSTRING String Function

Syntax 1: This syntax uses the SUBSTRING function with the column name of the SQL table:

In the syntax of substring function, we have to define the name of that column on which we want to execute it. Here, the Length_of_string parameter is optional. If it is omitted, then this function shows the whole string from the starting index value.

Syntax 2: This syntax uses the SUBSTRING function with the string:

Syntax 3: This syntax uses the SUBSTRING function with a single character:

Examples of SUBSTRING String function

Example 1: The following SELECT query shows the characters from the 17^th position of the given string.

This SQL query returns the 25 characters with spaces after the 17^th position in the string.

Output:

Example 2: The following SELECT query shows the characters from the -16^th position of the given string:

This SQL query shows the five characters from the last 16^th position of the string.

Output:

Example 3: The following SELECT query shows all the characters from the 5^th position of the string.

Output:

Example 4: The following SELECT query shows the single character from the 6^th position of the string.

Output:

Example 5: This example uses the SUBSTRING function with the SQL table

In this example, we are going to create a new SQL table on which we want to execute the SUBSTRING function.

The syntax for creating the new table in the SQL database is as follows:

The following CREATE statement creates the Student_Grade table:

The below INSERT queries insert the records of students with grades and marks in the Student_Grade table:

The following SELECT statement displays the inserted records of the above Student_Grade table:

Query 1: The following SELECT query uses the SUBSTRING function with the Last_Name column of the above Student_Grade table:

This SQL statement shows the four characters from the 2^nd position of the Last name of each student.

Output:

Query 2:The following SELECT query uses the SUBSTRING function with the Last_Name column of the above Student_Grade table:

This SQL statement shows the two characters from the third last position of the Last name of each student.

Output: