This article will provide an understanding of different ways to use substring in MySQL.
Introduction
MySQL is an open-source relational database management system. It is a widely used relational database management system in the entire world. MySQL like any other database can store different types of data. One of the most used data types is “String”. Developers widely use it in storing data as well as in different formatting operations. One of the key requirements we will always come across is to derive a part of the string. MySQL provides a “SUBSTRING” function to extract a substring from a string. My SQL has below options for extracting the string:
- SUBSTRING
- SUBSTR (A SYNONYM FOR SUBSTRING)
- SUBSTRING_INDEX
Let us start understanding how the substring works.
SUBSTRING
The SUBSTRING function has different variations to extract the substring from the actual string.
- SUBSTRING (string, position)
This function returns the substring of the string from the position mentioned. This function is most useful when one needs to specify the exact position of the string to start extracting from. First, let us understand the positions of the string. Below is the sample string:
THIS IS A SAMPLE
The position considered in SUBSTRING is always from 1. So ‘T’ letter of ‘THIS’ will be the first letter. So, the positions will be as listed below:
T = 1
H = 2
I = 3
S = 4
SPACE = 5
I = 6
S = 7
SPACE = 8
A = 9
SPACE = 10
S = 11
A = 12
M = 13
P = 14
L = 15
E = 16
Please note that we are going to use string throughout the article. So, we will refer to these positions very frequently. Now, let us say we want to extract just “Sample” from the entire string. In our case, we would want it from position 11 to get it extracted. So, the function will look like something:
SUBSTRING(‘THIS IS A SAMPLE’,11)
To derive the result as follows:
Please make sure that to not start with 10, a position behind the actual starting point as it will give you unwanted extra character or in this case ‘ ‘ a space.
MySQL also provides an option to mention the position in a negative integer. Before we understand how to use that, let us understand how the negative positions look like as follows:
T = -16
H = -15
I = -14
S = -13
SPACE = -12
I = -11
S = -10
SPACE = -9
A = -8
SPACE = -7
S = -6
A = -5
M = -4
P = -3
L = -2
E = -1
As we can see the positions start from the last of the character towards the start of the character. Let’s take an example. Let’s say we want to extract “SAMPLE” from the string. To do so, we need to use the SUBSTRING function as follows:
SUBSTRING(‘THIS IS A SAMPLE’,-6)
To get an output as follows
In this way, we can use the negative integer to get the same results. However, it will be a little tricky to count negative values at times. So, I would recommend caution while using it. There is
- SUBSTRING (string FROM position)
This is a variation for the above example for SUBSTRING(string, position). It works in the same way. One needs to mention the string to be extracted from. FROM is the keyword and position is from where you want to extract. Continuing from our last example, let’s say in case we want to extract “SAMPLE”, then it would be as follows:
SUBSTRING(‘THIS IS A SAMPLE’ FROM 11)
We can use the same for negative integers as well. It would be as follows:
SUBSTRING(‘THIS IS A SAMPLE’ FROM -6)
This variation is more like SQL where we use the same phrases in the select query like “Select * from xyz”. For the developer, it will be more familiar. However, both of these variations have a limitation. It can only allow you to extract a string from a certain starting point. It doesn’t allow you to extract the string from any starting point up to any specified endpoint.
- SUBSTRING (string, position, length)
This is where this variation will help you to extract the exact string. In this variation, we can mention the “string” from where the substring needs to be extracted, “position” from where to start, and “length” for how many characters it needs to be extracted. Let’s continue with the example “THIS IS A SAMPLE”. Let us say I want to extract ‘IS’ from the string. The way to do that will be
SUBSTRING(‘THIS IS A SAMPLE’,6,2)
To extract the below value
Let us take another example. Let’s say I want to extract the “A SAMPLE” from the entire string. The function should be as follows:
SUBSTRING(‘THIS IS A SAMPLE”, 9, 8)
Where 9 will be the starting position with 8 more characters to extract with the below results:
As like what we did in the earlier example, we can even use a negative integer in this variation as well to extract the string as follows:
SUBSTRING(‘THIS IS A SAMPLE, -11,2)
To extract the below value:
-11 is the position from the right where the starting point would be. And 2 characters will be extracted towards the right as shown above. We need to be careful with mentioning the length as it would be considered right from the position mentioned.
- SUBSRTING (string FROM position FOR length)
Like the above variation, we can use another variation where we mention “string” to extract from FROM keyword from which position “position” to provide a starting position FOR keyword indicating for which length “length” for the number of characters.
Let’s say we way to repeat the above example where we want to extract “IS” from the entire string. Then the way we should use the functions will be as follows:
SUBSTRING(‘THIS IS A SAMPLE’ FROM 6 FOR 2)
To get values as shown below:
You can also use it for negative integers in the same way have used it earlier. For instance,
SUBSTRING(‘THIS IS A SAMPLE’, FROM -11,2)
Will provide you “IS” as extracted string from the entire string.
These are a few key things to be careful of:
- In SUBSTRING(str, pos) you need to mention a positive or a negative integer. Mentioning 0 will error you out.
- In SUBSTRING(str, pos, length), the length should be a positive integer. Mentioning 0 will return no value as it means you are trying to fetch 0 lengths of characters.
- In case no parameters are mentioned in functioned for pos and length, the function is fail-safe and returns NULL
SUBSTR
SUBSTR is a synonym and can be used identically as a substring. Please use the below signatures
SUBSTR(string, pos)
SUBSTR(string FROM pos)
SUBSTR(string, pos, len)
SUBSTR(string FROM pos FOR len)
SUBSTRING_INDEX
SUBSTRING_INDEX(String, delimiter, count)
Where “String” is the source string
“delimiter”, is the character that has multiple occurrences in the entire string “count” is the number of occurrences to get the string before or after from. If the count mentioned is positive, then it will extract from the left of the occurrence mentioned as a count. If the count mentioned is negative, it will extract from the right.
This function can be used when we need to extract information based on the position of the delimiter. Let’s say we have a website URL and I would like to extract the domain name from the website URL. Let’s say we want to extract “sqlshack” from “https://www.sqlshack.com/”. We can achieve it in two different steps:
SUBSTRING_INDEX(‘https://www.sqlshack.com/’,’.’,2)
The above function will be able to derive anything when before the 2nd occurrence which will result in the below result:
However, this doesn’t give us a domain name. So, we should use another SUBSTRING to get the values from the right of the count occurrence as follows.
SUBSTRING_INDEX(SUBSTRING_INDEX(‘https://www.sqlshack.com/’,’.’,2),’.’,-1)
which will result in as follows:
Thus, in this way you can use substring in multiple permutations and combinations with different functions to extract the substring from.
Conclusion
- In this article, we learned to extract specified parts of a string using Substring in MySQL.
- There are different variations, permutations, and combinations to use MySQL Substring.
- We can use nested substrings to get very specified results.
He has worked internationally with Fortune 500 clients in various sectors and is a passionate author.
View all posts by Rahul Mehta
- Finding Duplicates in SQL - February 7, 2024
- MySQL substring uses with examples - October 14, 2023
- MySQL group_concat() function overview - March 28, 2023