The SUBSTRING function in MySQL is used to extract a substring from a given string. It allows you to specify the starting position (index) and the length of the substring you want to extract.
Syntax
The basic syntax of the SUBSTRING function is as follows:
SUBSTRING(str, start, length)
str: This is the input string from which you want to extract the substring.
start: This is the starting position from which the substring extraction should begin. It is an integer value, and the counting starts from 1.
length: This is the length of the substring to be extracted. It is an optional parameter, and if not specified, the function will return the substring from the starting position to the end of the input string.
Examples
Here are some examples to illustrate the usage of the SUBSTRING function:
Extract a substring starting from the 3rd position to the end of the string.
SELECT SUBSTRING('Hello, World!', 3);
Output: llo, World!
Extract a substring starting from the 7th position with a length of 5 characters.
SELECT SUBSTRING('MySQL is powerful', 7, 5);
Output: is po
If the length parameter is not specified, the function returns the substring from the specified starting position to the end of the string.
SELECT SUBSTRING('Extract from here', 10);
Output: rom here
The SUBSTRING function is often used in combination with other string functions to manipulate and extract information from strings in a database. It is particularly useful when dealing with text data and when you need to retrieve specific portions of a string based on certain criteria. Keep in mind that the position and length parameters must be valid for the given string; otherwise, the function may return unexpected results or produce an error.