What is the SQL SUBSTRING function?
For example:
In certain scenarios, we have columns that store large strings. However, this may be required to get only part of the large string for certain purposes.
For getting part of a string from a long string, you may use the built-in function of SQL i.e. SUBSTRING.
Syntax of using SUBSTRING function in SQL Server
The SUBSTRING function can be used as follows:
Where:
Arguments | Description |
expression | An expression can be a string column name etc. |
start | The start parameter species from where the SUBSTRING function should start extracting in the given expression. |
length | The length specifies the number of characters to extract in the given expression. |
An example of the SUBSTRING function
Generally, you will use the SUBSTRING function to get the part of the string from the table data column. However, for its simple usage, let me show an example of a constant string in the SUBSTRING function.
In the SUBSTRING function, the expression “Hello World” is assigned and start and length arguments are given as follows:
Query:
SELECT SUBSTRING('Hello World', 7, 5) AS "The SUBSTRING";
The output:
The value specifies to start extracting string from the letter ‘W’ and go till letter 5 i.e. World.
The example of SUBSTRING SQL with table data
Now, let us look at a real example of using the SUBSTRING function. For that, we have a phone field in our example table, sto_employees that is used to store the staff information.
The phone is stored in the following format:
123-4567-457896
By using the SUBSTRING function, we will get the second part of the phone number for each employee.
For clarification, the original data of the phone along with employee names are also fetched along with data using the SUBSTRING function.
Have a look at the SUBSTRING query and result set:
SELECT emp_name, phone, SUBSTRING(phone, 5, 4) AS "Phone four digits" FROM sto_employees;
The result-set:
You can see the third column displays the second part of the phone number.
Similarly, you may get the third part only by changing the starting position and length argument as follows:
SELECT emp_name, phone, SUBSTRING(phone, 10, 6) AS "Phone Third Part" FROM sto_employees;
The result-set:
The third column shows the last six digits of the phone numbers for each employee.
What if a negative starting position is given?
The following example shows using the MySQL SUBSTRING function with a negative starting position.
The Query:
SELECT Product_Name, SUBSTRING(Product_Name, -1, 2) FROM `tbl_products`;
The result:
You can see, the SUBSTRING function returned the last character from each product name.
How to get a substring from right in SQL Server?
If you require getting the substring from the right of a string then SQL Server has another function called RIGHT. For example:
SELECT RIGHT('Learn SQL', 3);
An example of using MySQL MID function
As mentioned earlier, the SUBSTR and MID functions in MySQL database can also be used to extract the substring from a specified string.
The following query shows using the MID function where we will get the four-length substring started at position three in the product name column.
The MID query:
SELECT Product_Name, MID(Product_Name, 3, 5) FROM `tbl_products`;
You can see that it yielded the same result as we used the SUBSTRING function.
Using the MySQL SUBSTR function
Finally, see an example of SUBSTR MySQL function for the same table and positions in the products table:
SELECT Product_Name, SUBSTR(Product_Name, 3, 5) AS "Product Substring" FROM `tbl_products`;
The resultset:
You can see the same result in the above resultant table graphic.