MySQL SUBSTRING Function with Examples


How to Use the MySQL SUBSTRING Function

In this tutorial, you'll learn how to use the MySQL SUBSTRING function. SUBSTRING is a function to extract a substring from a given string.

MySQL SUBSTRING() Syntax

The syntax of the SUBSTRING function is as follows:

SUBSTRING(string FROM position FOR length);

or:

SUBSTRING(string, position, length);

Arguments

  • String: Required. It is the string to extract from
  • Position: Required. It is the start position that you want to extract a substring. SUBSTRING extracts from the beginning of the text if the position is a positive number, and extracts from the end of the string if the position is a negative number.
  • Length: Optional. It is the number of characters to extract. If omitted, SUBSTRING returns the whole string, starting at the specified position.

Examples

Example 1: Both FROM and FOR Keywords Omitted

The following statement extracts a substring (3 characters), starting at 11th position:

SELECT SUBSTRING("This site provides a lot of tutorials.", 11, 3);

Output:

pro

Example 2: Using Both FROM and FOR Keywords

The following statement extracts a substring (8 characters), starting at 11th position:

SELECT SUBSTRING("This site provides a lot of tutorials." FROM 11 FOR 8);

Output:

provides

Example 3: FOR Keyword Omitted

The following statement extracts a substring, starting at 11th position:

SELECT SUBSTRING("This site provides a lot of tutorials." FROM 11);

Or you can write as follow:

SELECT SUBSTRING("This site provides a lot of tutorials.", 11);

Here, FROM is omitted.

Output:

Both statements return the same result as :

provides a lot of tutorials.

Example 4: Extract a Substring from the End of a String

The following statement extracts a substring from the end of the string, starting at 10th position:

SELECT SUBSTRING("This site provides a lot of tutorials.", FROM -10);

If you omit the FROM keyword, you can write as follows:

SELECT SUBSTRING("This site provides a lot of tutorials.", -10);

Output:

tutorials.

In this tutorial, you've learned how to use the MySQL SUBSTRING function. SUBSTRING allows you to extract a sequence of characters from a given string.

Leave a Reply

Your email address will not be published. Required fields are marked *