MySQL Substring Function

MySQL substring is a MySQL string function that gives you the ability to query and return from a string, a substring. For instance, if you want to grab a small piece of text from a larger piece, the substring function is the easiest way to do it. The syntax of this MySQL string function differs depending on how you want to use it.

Below we look at the different ways of using the function substring and what the different syntax will give you.

This first example shows the most basic usage of substring syntax as follows:

SUBSTRING (str,pos)

In the case above, the “str” represents the string while “pos” represents the position from where the substring will start. To see the function in action, let us use a real-life function below:

SELECT SUBSTRING ('Cats and dogs',6);

The result for the above function will be: and dogs

Simply put, the function starts at the sixth position and picks a substring from the string Cats and dogs. In our second example below, we will make it a bit more complex by using the FROM clause. The syntax with the FROM clause takes the format:

SUBSTRING (str FROM pos)

The FROM clause, in this case, is borrowed from standard SQL and the syntax does not use any commas. Using the same sample we used in our basic syntax, let us change the syntax by using the FROM clause:

SELECT SUBSTRING ('Cats and dogs' FROM 6);

The result, in this case, is the same as in the first statement. There are many other ways to use this function and among them are:

Specifying substring length:

SUBSTRING (str, pos, len) as
SELECT SUBSTRING ('Cats and dogs', 6, 3);

The result for this is “and” as it specifies to start from the position six and only take 3 strings forward.

Specifying length using FROM and FOR:

SELECT SUBSTRING ('Cats and dogs' FROM 6 FOR 3);

The result for this is the same as with the ‘len’ specifier.

The substring function also allows you to count backward.

SELECT SUBSTRING ('Cats and dogs', -6);

The result for the backward count, in this case, returns “d dogs”.

In all the functions we above, there are two synonyms for SUBSTRING that you can use to yield similar results. These are “SUBSTR()” and “MID()”.

Working With MySQL SUBSTRING_INDEX()

Another MySQL string function is MySQL SUBSTRING_INDEX which allows you to get from a substring from a string. However, with MySQL SUBSTRING_INDEX, you have to specify the number of occurrences of the delimiter before which the substring will be taken.

The syntax for MySQL SUBSTRING_INDEX is:

SUBSTRING_INDEX (str,delimiter,n)

In the syntax above, ‘str’ represents the string from which you will extract a substring while the delimiter is a string whose function is acting as a delimiter. When searching for the delimiter, the match that the function performs is case-sensitive.

The last character, ‘n’, represents an integer that specifies how many times the delimiter occurs. This integer can either be positive – where it returns characters starting from the left of a string to the number it specifies, or negative – where the characters returned start from the extreme right to the nth value of delimiter occurrences.

For instance, assuming we have the function as below:

SELECT
		SUBSTRING_INDEX ('Hello Wordl', '1', 1);

In the example above, both the delimiter and the ‘n’ value are 1. In this case, the function will return all the characters up to the first occurrence of the delimiter1 as with the result being – ‘He’.

If we were to maintain the delimiter and 1 and only vary the n value to specify the number of times the delimiter occurs, we would have:

Where n=2:

Hel

Where n=3:

Hello Wor

If we were to use negative occurrences in the above functions, we would have different results.

SELECT
		SUBSTRING_INDEX('Hello Wordl', '1', -1) result1
    SUBSTRING_INDEX('Hello Wordl', '1', -2) results2
    SUBSTRING_INDEX('Hello Wordl', '1', -3) result3

The results, in this case, would be:

Result1 = d

Result2= o World

Result3 = lo World

With this knowledge, you can now use the substring function in MySQL to get a substring from a string. For even better functionality, you can specify the number of occurrences of the delimiter before which to get a substring from a string.