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 |
| tutoraspire 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 | MurthalHarya#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 |