Substring in MySQL – Syntax and Examples

In this article, we are going to talk about the MySQL SUBSTRING() function with its variations. Here, you will find a detailed guide with the basic syntax and examples of how to use this function. Moreover, we will demonstrate the benefits of MySQL coding in a handy IDE – dbForge Studio for MySQL that will help you save a lot of time and energy.

If you need to extract a substring from a certain string, you can use the SUBSTRING() function. To do that, you need to specify its position. MySQL returns the substring as specified in the function. SUBSTRING() usually includes three parameters: the first one is the string itself, the second specifies the starting point from where you are willing to trim the data, and the third determines the length of the substring (the number of characters we are trying to extract).

Contents

• SUBSTRING() with position
  • Example 1. SUBSTRING without count
  • Example 2. SUBSTRING with negative index position
• SUBSTRING() with position and length
  • Example 3. SUBSTRING between two characters
• REGEXP_SUBSTR()
  • Example 4. REGEXP_SUBSTR with basic parameters
  • Example 5. REGEXP_SUBSTR with specifying a starting position
  • Example 6. REGEXP_SUBSTR with occurrence argument
• SUBSTRING_INDEX()
  • Example 7. SUBSTRING_INDEX: Character search
  • Example 8. SUBSTRING_INDEX: String search
  • Example 9. SUBSTRING_INDEX: Reverse order search
• Conclusion

SUBSTRING() with position

The position parameter specifies where the beginning of the substring is.

Syntax

The basic syntax in this case will look somewhat like this:

SUBSTRING(string,position);

Explanation

The function contains two parameters:

1. The string parameter specifies the source string that you are going to extract the substring from.
2. The position is an integer that specifies the position of a character in the substring. It can be either positive or negative.

If the position of the integer is positive, the function extracts the substring from the beginning of the string. On the contrary, if the position of the integer is negative, the SUBSTRING() function starts counting from the end of the string.

Note: If you specify zero as a position parameter, MySQL will return an empty string.

Example 1. SUBSTRING without count

As an example, let us imagine you need to extract a substring from the ‘Lorem ipsum dolor sit amet’ string starting from the seventh character:

SELECT SUBSTRING('Lorem ipsum dolor sit amet', 7);

To make the examples more visual, we are going to use a full-fledged MySQL GUI. Besides a user-friendly interface, dbForge Studio for MySQL can boast a broad set of features and increased performance, such as:

• Code formatting – focus on the code itself without having to worry about how it looks. dbForge Studio for MySQL will beautify your code so that it not only works but also looks flawless. The feature is highly customizable, thus, you can easily configure the formatting behavior to fit even the most sophisticated requirements.
• Code completion – speed up your code-writing performance with the feature that literally anticipates your next move. Auto-completion pop-ups and query hints greatly facilitate routine coding and eliminate typos and casual errors.
• Code editing – the feature helps you navigate the code by allowing you to collapse a certain region of it, identify different code categories by color, and more.

As a result, you will get ‘ipsum dolor sit amet’.

Example 2. SUBSTRING with negative index position

We have already seen how the SUBSTRING() function works with the positive position value. However, if you add a negative sign to the position number in the function, things will change:

SELECT SUBSTRING('Lorem ipsum dolor sit amet', -7);

On executing the above query, you will get ‘it amet’ as a result.

SUBSTRING() with position and length

In the previous section, we have been talking about the position parameter of the SUBSTRING() function that only specifies the first character of the substring to be extracted. However, what if you need to specify the length of the substring in question? This is when the eponymous length parameter takes the stage. By using both position and length parameters, you can determine the beginning and end of the extracted substring.

Syntax

The query will not undergo much change as compared to the previous example:

SUBSTRING(string,position,length);

Explanation

Just like the previously mentioned parameter, length can take both positive and negative positions. When you specify it, you are limiting the output of the query.

Example 3. SUBSTRING between two characters

Let us take a look at another SUBSTRING() function example. Again, using the ‘Lorem ipsum dolor sit amet’ string, extract five characters starting from the first one:

SELECT SUBSTRING('Lorem ipsum dolor sit amet',1,5);

MySQL returns only ‘Lorem’ on executing the query.

REGEXP_SUBSTR()

When talking about SUBSTRING(), we cannot leave out the REGEXP_SUBSTR() function. In MySQL, it returns the substring that matches the given regular expression pattern. If there are no matches to be found, NULL is returned.

Syntax

In terms of syntax, it looks like this in its basic form:

REGEXP_SUBSTR(string, patern[, position[, occurrence[, match_type]]]);

Explanation

In the syntax above, string represents the input string while pattern is the regular expression pattern for the substring. The rest of the arguments are optional:

• position – allows you to specify a position within the string to start the search. If omitted, the substring starts at the beginning of the string.
• occurrence – allows you to specify which occurrence of the match to search for. Unless specified otherwise, occurrence 1 is used by default.
• match_type – allows you to refine the regular expression:
  • c – case-sensitive matching.
  • i – case-insensitive matching.
  • E – enable subexpression.
  • m – a multiple-line mode that recognizes line terminators within the string. By default, this function matches line terminators at the start and end of the string.
  • n – modify the . (dot) character to match line terminators. By default, it will stop at the end of a line.
  • u – Unix-only line endings that recognize only the newline character by the ., ^, and $ match operators.

Example 4. REGEXP_SUBSTR with basic parameters

Say, you are now searching for one word in a string. This action requires a simple query execution:

SELECT REGEXP_SUBSTR ('Lorem ipsum dolor sit amet', 'd..or');

MySQL returns ‘dolor’ as it matches the set requirements.

Example 5. REGEXP_SUBSTR with specifying a starting position

Now, we are going to challenge MySQL to extract a substring, but only from a certain part of an initial string. To illustrate this particular case, let us modify the initial string a little:

SELECT REGEXP_SUBSTR ('Lorem ipsum dolor sit dulor', 'd.lor', 18);

As you can see, MySQL returns ‘dulor’ on query execution, even though ‘dolor’ would have matched the specified criteria as well. The thing is that MySQL was only looking for matches after the eighteenth character, which is after the said ‘dolor’.

Example 6. REGEXP_SUBSTR with occurrence argument

There are also cases when you are looking for a certain word in a string, but it occurs more than once. For such situations, use the occurrence parameter:

SELECT REGEXP_SUBSTR ('Lorem ipsum dolor sit amet, amit, amat', 'am.t', 1, 3);

As a result, you will get ‘amat’ as it is the third match.

SUBSTRING_INDEX()

Another handy function in MySQL is SUBSTRING_INDEX(). It can be used to return a substring from a string before a specified number of occurrences of the delimiter.

Syntax

The basic SUBSTRING_INDEX() syntax:

SUBSTRING_INDEX(string, delimiter, count);

Explanation

In the syntax above, there are three parameters:

• string – the source string you would like to extract a substring from.
• delimiter – acts as a delimiter, as the name suggests. By default, this function performs a case-sensitive search.
• count – identifies how many times to search for the delimiter. It can be both a positive or negative number. If positive, this function returns everything to the left of the delimiter. If negative – on the right-hand side.

Example 7. SUBSTRING_INDEX: Character search

The SUBSTRING_INDEX() function allows you to extract everything that comes before or after a particular character.

SELECT SUBSTRING_INDEX('Lorem ipsum dolor sit amet', 'i', 2);

If you use ‘i’ as a delimiter and a positive number 2 as a count parameter, MySQL will return ‘Lorem ipsum dolor s’. As you can see, the string interrupts right before the second ‘i’, as specified in the query above.

Example 8. SUBSTRING_INDEX: Reverse order search

If you slightly change the query by adding a negative 2 to it, the outcome will be rather different: ‘psum dolor sit amet’.

SELECT SUBSTRING_INDEX('Lorem ipsum dolor sit amet', 'i', -2);

Example 9. SUBSTRING_INDEX: String search

SUBSTRING_INDEX() works not only for single characters but also for words. Let us say, you would like to extract only a part of a string that comes before the second word ‘ipsum’ from ‘Lorem ipsum dolor sit ipsum’:

SELECT SUBSTRING_INDEX('Lorem ipsum dolor sit ipsum', 'ipsum', 2);

In this case, 'Lorem ipsum dolor sit ' will be returned as a result.

Conclusion

To sum up, we have gone through the basics of the SUBSTRING(), REGEXP_SUBSTR(), and SUBSTRING_INDEX() functions usage, reviewed the simplest syntax, and provided you with examples on the basis of a universal IDE for MySQL and MariaDB – dbForge Studio for MySQL. It is a full-fledged MySQL GUI that helps create and execute queries, develop and debug stored routines, automate database object management, compare and synchronize databases, analyze table data, and much more. Download a fully-functional 30-day free trial version of the tool for evaluation purposes.