Home » SUBSTR Function in SQL

SUBSTR Function in SQL

by Online Tutorials Library

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

You may also like