MySQL Substring

Syntax of MySQL Substring() Function

The syntax of the MySQL substring() function is:

OR

Parameters of MySQL Substring() Function

• Input String:
  This string represents an original input string to apply substring extraction.

• Starting Index position:
  The index represents the starting position where you can start retrieving the part of a string. This variable can have positive or negative values. If positive, this SUBSTRING() function iterates from the beginning of the string, if negative, the function iterates and gets the substring from the end of the string. Note that MySQL indexing starts at 1 (not 0).

• Length variable:
  The length parameter represents the length of a substring in characters. This length parameter is optional and if it is not passed as a parameter in the function, the entire string will be returned, starting at the specified index position and ending with the end of the string.

Return Value of MySQL Substring() Function

The return value of this MySQL Substring() function is a string data type that is part of an original string.

How does the MySQL Substring() Function Work?

The Substring() function works by extracting a particular length of the string from a specified position. Let's see different ways of using this MySQL Substring() Function:

1. select substring(String, index);

This function of MySQL() returns a substring of a string from a given index position till the end of the string.

2. Select substring(String from index);

This way of returning a part of the string is similar to the above way where a part of a string is extracted from a given index position till the whole length of the original string.

3. Select substring(String, index, len);

This function returns a substring starting from a given index of a specific length len.

4. Select substring(String from index for len);

This function works similarly to the above syntax and returns a part of a string starting from an index with the length len.

We will see various examples of the substring() function in the upcoming section.

Examples

1. Extract a substring from a string where starting index position and length is passed as a parameter:

   Query:

   Output:

   Explanation:
   This function will return the substring from the 7th position and will retrieve the 3 character long string.

2. Extract a substring from a string where starting index position and length is passed with an alternate query:

   Query:

   Output:

   Explanation:
   This function will return the substring from the 7th position of 3 character long string.

3. Extract a substring when no length parameter is passed in the function:

   Query:

   Output:

   Explanation:
   This function will return the string from the 7th position and will extract it till the end of the original string as no parameter is passed for length.

4. Extract a substring when no length parameter is passed in the function with an alternate query:

   Query:

   Output:

   Explanation:
   This function will also return the string starting from the 7th position and will extract it till the end of the original string as there is no upper limit on the length.

5. Extract a substring when the index position parameter is negative:

   Query:

   Output:

   Explanation:
   This function has a negative position variable and thus traverses the input string in a reverse direction.

6. Extract a substring from the text column of a table:

   You can also use this substring() function to retrieve substrings from the text column in a given MySQL table. Let's suppose we have a student_details table, having ROLL_NO, NAME, and MARKS as the fields, and some data has been stored in these fields. Let’s see how our table’s data look by making use of the SELECT command:

   ROLL_NO	NAME	MARKS
   1	Rita Goyal	67
   2	Kanika Mittal	91
   3	Jyoti Sharma	21
   4	Reshma Kumari	100

   Now, if you want to apply the MySQL substring function to each of the names present in the above table, you can use the below query:

   Query:

   Output:

   Please note, there is a space after the yoti string in the above output contributing to a 5 characters long string.

7. Extract a substring from the text column of a table when the index position parameter is negative:

   Query:

   Output:

   Explanation:
   The result of this query would be similar to the above queries for retrieving the substrings of all the names in the student_details table. In this case, as the position variable is negative, the substring extraction will take place from the end of the string and it will slice out a string of length 3 in the output.

8. Extract a substring when the length parameter is negative:

   Query:

   Output:

   Explanation:
   Here the length variable provided is incorrect and inconsistent. But there will be no error and the result of this query would be an empty string.

9. Extract a substring when the length parameter is greater than the length of an original string:

   Query:

   Output:

   Explanation:
   In the above query, the length parameter is greater than the string length, and thus the entire string will be extended from the starting index position.

See Also

• MySQL SUBSTRING_INDEX() Function
• MySQL TRIM() Function
• MySQL UCASE() Function

MCQs

1. What is the function used in MySQL for extracting a substring of a string?

   • Option - 1: UPPER()
   • Option - 2: LEFT()
   • Option - 3: SUBSTRING()
   • Option - 4: COUNT()

   Correct Answer: Option - 1.

2. What would be the output of this query SELECT SUBSTRING("Learning session of MySQL", 3, 9);

   • Option - 1: earnings
   • Option - 2: arning se
   • Option - 3: earning se
   • Option - 4: arning ses

   Correct Answer: Option - 2.

3. What would be the correct query for extracting "Life Is Fun" substring as an output?

   • Option - 1: SELECT SUBSTRING("Life Is Fun when We Are Happy" FROM 1 FOR 9)
   • Option - 2: SELECT SUBSTRING("Life Is Fun when We Are Happy" FROM 3 FOR 8)
   • Option - 3: SELECT SUBSTRING("Life Is Fun when We Are Happy" FROM 1)
   • Option - 4: SELECT SUBSTRING("Life Is Fun when We Are Happy" FROM 1 FOR 11)

   Correct Answer: Option - 4.