MID Function in SQL

The MID string function in the 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 MID function with the tables. The MID function is similar to the SUBSTR string function.

Syntax of MID String Function

Syntax1: This syntax uses the MID 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 MID() function. Here, the Number_of_Characters argument is optional. If it is omitted, then this function extracts the whole string from the starting index value.

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

Syntax2: This syntax uses the MID function with a single character:

Examples of MID String function

Example 1: The following SELECT query shows the characters from the 17^th position of the given string.

This SQL query returns the 24 characters with spaces after the 17^th position in the string.

Output:

website for professionals

Example 2: The following SELECT query shows the characters from the -17^th position of the given string:

This SQL query shows the five characters from the last 17^th position of the string.

Output:

website for professionals

Example 3: The following SELECT query shows all the characters from the 5^th position of the string.

Output:

Delhi IS the Capital OF India

Example 4: The following SELECT query shows the single character from the 8^th position of the string.

Output:

n

Example 5: This example uses the MID function with the SQL table

In this example, we are going to create a new table on which we want to execute the MID function.

The syntax for creating the new SQL table is mentioned in the below block:

The following CREATE statement creates the Schoolboy_Marks table:

The below INSERT queries insert the records of college Faculties in the Schoolboy_Marks table:

The following SELECT statement displays the inserted records of the above Schoolboy_Marks table:

Query 1: The following SELECT query uses the MID function with the Schoolboy_Last_Name column of the above Schoolboy_Marks table:

This SQL statement shows the four characters after the 2^nd position of the Last name of each student.

Output:

Query 2:The following SELECT query uses the MID function with the Schoolboy_Last_Name column of the above Schoolboy_Marks table: