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 from which to extract a substring
  • position: Required. The start position that you want to extract a substring. 
  • SUBSTRING extracts from the beginning of the text if position is a positive number.
  • SUBSTRING extracts from the end of the string if the position is a negative number.
  • length: Optional. Indicate the number of characters to extract. SUBSTRING returns the whole string, starting at the specified position if omitted.

Examples

Example 1: Omit both FROM and FOR keywords

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: Use both FROM and FOR

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: Omit FOR keyword

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

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

Or you can omit the FROM keyword, as the following statement shows:

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

Output:

Both statements return the same value as follows:

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);

Or you can omit the FROM keyword, as the following example shows:

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() extracts a sequence of characters from a given string starting at a specified position.


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment