How to use MySQL Substring function

Mysql has a variety of built-in functions for different purposes, one of them is the function of the substring. The function of substring deals with the “string” data type and it is used in database management to extract the string from the tables. Now what the string is? The string is the collective combination of the characters. For example, “Hello LinuxHint” and “KHG1234” are strings.

In this article, we will understand what is the substring function and how it can be used in MySQL in managing the data in the databases.

What is MySQL substring

MySQL has a variety of functions that perform different tasks, similarly, Substring is a function that is used in MySQL to return or to extract the string from strings in any table. The general syntax is as:

Or:

If we explain the parameters used in these general syntaxes then they will be as; string is the collection of the characters, start means the starting point from where it is supposed to extract the string and length is the number of characters that are to be extracted. To understand the function of the substring, we take an example of the table and use the following commands to extract strings from it. We will open the terminal, and type “mysql” to enter the MySQL environment.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-01.png" data-lazy- height="326" src="data:image/svg xml,” width=”904″>

Open the list of databases, present in MySQL:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-02.png" data-lazy- height="576" src="data:image/svg xml,” width=”410″>

Open the database, “company”:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-03.png" data-lazy- height="112" src="data:image/svg xml,” width=”336″>

Now, I will show the tables present in this database as.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-04.png" data-lazy- height="312" src="data:image/svg xml,” width=”546″>

In the output, we can see the database, company, contains only one table which is named “linuxHint_employees_data”. Now we will access this table to display its contents:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-05.png" data-lazy- height="360" src="data:image/svg xml,” width=”904″>

Now we can display the contents of the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-06.png" data-lazy- height="356" src="data:image/svg xml,” width=”816″>

We say that we want to extract the string “John”, from the table using the substring function then we will use the code according to the syntax we discussed above and we will use “AS ExtractString ” so it returns the value in the form of string from the table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-07.png" data-lazy- height="220" src="data:image/svg xml,” width=”904″>

In this command, we use the terminal to extract a string from the column named employee_name, start from the first character, and extract the next 5 characters. To understand it more, let us say we want to extract the 2 characters from the column, named employee_name but it starts from the third character so the command will be.

As we know it will extract only “hn” and “ew”.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-08.png" data-lazy- height="220" src="data:image/svg xml,” width=”904″>

Also, we run the following command to extract the string.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-09.png" data-lazy- height="318" src="data:image/svg xml,” width=”902″>

Let us explain what happened in the above command and how this output came. We ask the terminal using this function of MySQL that returns the string characters from the string “John” and starts from its third position from the starting point, ve sign tells it to start from the starting point and extract the next two characters. So in the string “John”, if we start from the third position from the starting point it will start from “h” and if we extract two characters next to it then it will be “hn”. This is shown in the output.

Now let us run another command to extract “wa” from the string “Steward” using the starting point from the end of the string so for it we will use the -ve sign and the command will be.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/substring-function-mysql-10.png" data-lazy- height="300" src="data:image/svg xml,” width=”904″>

Conclusion

Substring is a function of MySQL which is used to extract either the whole string or a few characters of the string. In this write-up, we tried to explain to you what a substring function is and for which purpose it is used. We also discussed two general syntaxes and then explained the use of both these syntaxes in detail with the help of examples. Hope this write-up will help you not in understanding the function substring but how it can be used.