SUBSTR Function in SQL
The SUBSTR string function in Structured Query Language shows the characters or sub-string from the specific index value of the original string. SQL also allows you to use the SUBSTR function with the tables.
Syntax of SUBSTR String Function
Syntax1: This syntax uses the SUBSTR function with the column name of the SQL table:
In this syntax, we have to define the name of that column on which we want to execute the SUBSTR() function. Here, the Length_of_string parameter is optional. If it is omitted, then this function extracts the whole string from the starting index value.
Syntax2: This syntax uses the SUBSTR function with the string:
Syntax2: This syntax uses the SUBSTR function with a single character:
Examples of SUBSTR String function
Example 1: The following SELECT query shows the characters from the 17th position of the given string.
Output:
website for professionals
Example 2: The following SELECT query shows the characters from the -17th position of the given string:
This SQL query shows the five characters from the last 17th position of the string.
Output:
website for professionals
Example 3: The following SELECT query shows all the characters from the 5th position of the string.
Output:
Delhi IS the Capital OF India
Example 4: The following SELECT query shows the single character from the 8th position of the string.
Output:
n
Example 5: This example uses the SUBSTR function with the SQL table
In this example, we are going to create a new table on which we want to execute the SUBSTR function.
In this example, we have to create a new SQL table through which we will execute the Concat() function on columns. The syntax for creating the new SQL table is mentioned in the below block:
The following CREATE statement creates the Student_Marks table:
The below INSERT queries insert the records of college Faculties in the Student_Marks table:
The following SELECT statement displays the inserted records of the above Student_Marks table:
Student_Id | Student_First_Name | Student_Middle_Name | Student_Last_Name | Student_Class | Student_City | Student_State | Student_Marks |
---|---|---|---|---|---|---|---|
4001 | Aman | Roy | Sharma | 4 | Chandigarh | Punjab | 88 |
4002 | Vishal | Gurr | Sharma | 8 | Murthal | Haryana | 95 |
4007 | Raj | singhania | Gupta | 6 | Ghaziabad | Uttar Pradesh | 91 |
4004 | Yash | Chopra | Singhania | 9 | Jaipur | Rajasthan | 85 |
4011 | Vinay | Sharma | Roy | 8 | Chandigarh | Punjab | 94 |
4006 | Manoj | Singhania | Gupta | 5 | Ghaziabad | Uttar Pradesh | 83 |
4010 | Ram | Raheem | Gupta | 9 | Lucknow | Uttar Pradesh | 89 |
Query 1: The following SELECT query uses the SUBSTR function with the Student_Last_Name column of the above Student_Marks table:
This SQL statement shows the four characters after the 2nd position of the Last name of each student.
Output:
Student_Last_Name | SUBSTR_2_4 |
---|---|
Sharma | harm |
Sharma | harm |
Gupta | upta |
Singhania | ingh |
Roy | oy |
Gupta | upta |
Gupta | upta |
Query 2: The following SELECT query uses the SUBSTR function with the Student_Last_Name column of the above Student_Marks table:
This SQL statement shows the two characters from the third last position of the Last name of each student.
Output:
Student_Last_Name | SUBSTR_-3_2 |
---|---|
Sharma | rm |
Sharma | rm |
Gupta | pt |
Singhania | ni |
Roy | Ro |
Gupta | pt |
Gupta | pt |