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:

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

Output:

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

Output:

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

Output:

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:

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:

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:

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: