Javatpoint Logo
Javatpoint Logo

SUBSTRING_INDEX Function in SQL

The SUBSTRING_INDEX string function shows all the characters before the specific symbol in the given string.

Syntax of SUBSTRING_INDEX String Function

Syntax1: This syntax uses the SUBSTRING_INDEX with the column name of the SQL table:

In the syntax, we have to specify the name of that column on which we want to perform the SUBSTRING_INDEX string function.

Syntax2: This syntax uses the SUBSTRING_INDEX function with the string:

Examples of SUBSTRING_INDEX String function

Example 1: The following SELECT query shows the four characters before the given symbol in the original word JAVA^TPOINT:

SELECT SUBSTRING_INDEX( 'JAVA^TPOINT', '^', 1) AS SUBSTRING_INDEX_4_characters;

Output:

SUBSTRING_INDEX_4_characters
JAVA

Example 2: The following SELECT query shows the SUBSTRING_INDEX 20 characters from the given string:

Output:

SUBSTRING_INDEX_before.symbol
JAVATPOINT is a

Example 3: The following SELECT query shows the 5 characters from the third position to the given symbol in the given 'congratulations' word:

Output:

[email protected]
NGRAT

Example 4: The following SELECT query shows the 20 characters from the fifth position to the given symbol in the given string:

Output:

SUBSTRING_INDEX_before#symbol
Delhi is the capital

Example 6: This example uses the SUBSTRING_INDEX function with the table in Structured Query Language.

To understand the SUBSTRING_INDEX function with SQL, we have to create the SQL table first using CREATE statement. The syntax for creating the new table in the SQL database is as follows:

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:


Schoolboy_Id Schoolboy_First_Name Schoolboy_Middle_Name Schoolboy_Last_Name Schoolboy_Class Schoolboy_City Schoolboy_State Schoolboy_Marks
4001 Ama.n Roy Sha#rma 4 Chandigarh Pun#jab 88
4002 Vish.al Gurr Sh#arma 8 Murthal Harya#na 95
4007 Raj. singhania Gupt#a 6 Ghaziabad Uttar Pr#adesh 91
4004 Yas.h Chopra Singh#ania 9 Jaipur Rajasthan# 85
4011 Vin.ay Sharma Roy# 8 Chandigarh Punjab# 94
4006 Man.oj Singhania Gup#ta 5 Ghaziabad Uttar Pra#desh 83
4010 Ra.m Raheem Gupt#a 9 Lucknow Uttar Pr#adesh 89

Query 1: The following SELECT query uses the SUBSTRING_INDEX function with the Student_First_Name column of the above Student_Marks table:

This SQL statement shows the characters from the first position to the # symbol in the first name of each student.

Output:

Student_First_Name SUBSTRING_INDEX_before.symbol
Ama.n Ama
Vish.al Vish
Raj. Raj
Yas.h Yas
Vin.ay Vin
Man.oj Man
Ra.m Ra

Query 2: The following SELECT query uses the SUBSTRING_INDEX function with the Student_Last_Name column of the above Student_Marks table:

This SQL statement shows the characters from the first position to the # symbol in the last name of each student.

Output:

Student_Last_Name SUBSTRING_INDEX_before#symbol
Sha#rma Sha
Sh#arma Sh
Gupt#a Gupt
Singh#ania Singh
Roy# Roy
Gup#ta Gup
Gupt#a Gupt

Query 3: The following SELECT query uses the SUBSTRING_INDEX function with the Student_Address column of the above Student_Marks table:

This SQL statement shows the characters from the second position to the given symbol in the state of each student.

Output:

Student_State [email protected]
[email protected] un
[email protected] arya
Uttar [email protected] ttar Pr
[email protected] ajasthan
[email protected] unjab
Uttar [email protected] ttar Pra
Uttar [email protected] ttar Pr






Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA