How to get SUBSTRING in SQL Databases?
The SQL SUBSTRING function is used to return a part of the string by specified position. For example:
SELECT SUBSTRING(‘SUBSTRING Examples’, 1, 9);
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 purpose.
For getting the part of a string from 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:
SUBSTRING ( expression ,start , length )
Where an expression can be a string column name etc. The start parameter species from where SUBSTRING function should start extracting in the given expression.
The length specifies the number of characters to extract in the given expression.
An example of SUBSTRING function
Generally, you will use the SUBSTRING function to get the part of 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:
|
1 2 3 |
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 phone along with employee names are also fetched along with using the SUBSTRING function. Have a look at the SUBSTRING query and result-set:
|
1 2 3 4 5 |
SELECT emp_name, phone, SUBSTRING(phone, 5, 4) AS "Phone four digits" FROM sto_employees; |
The result-set:
You can see the third column is displaying 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:
|
1 2 3 4 5 |
SELECT emp_name, phone, SUBSTRING(phone, 10, 6) AS "Phone Third Part" FROM sto_employees; |
The result-set:
The third column shows last six digits of the phone numbers for each employee.
What if a negative starting position is given?
The SUBSTRING function in MySQL database starts counting from the end of string if you provide a negative number. However, the MS SQL Server’s SUBSTRING function does not.
The following example shows using the MySQL SUBSTRING function with a negative starting position.
The Query:
|
1 2 3 |
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:
|
1 |
SELECT RIGHT('Learn SQL', 3); |
So, the RIGHT function takes an expression and length to be returned for the substring.
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:
|
1 2 3 |
SELECT Product_Name, MID(Product_Name, 3, 5) FROM `tbl_products`; |
You can see, it yielded the same result as we used SUBSTRING function.
Using the SUBSTR function
Finally, also see an example of SUBSTR MySQL function for the same table and positions in the products table:
|
1 2 3 |
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.