SQL SUBSTRING (Explained with 7 Queries)

What is the SQL SUBSTRING function?

The 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 purposes.

For getting part of a string from a long string, you may use the built-in function of SQL i.e. SUBSTRING.

Note: This function is called SUBSTRING in MS SQL Server while in MySQL database, this is called SUBSTR(), SUBSTRING, and MID function.

All these functions in MySQL database extract the substring (See last two examples).

Syntax of using SUBSTRING function in SQL Server

The SUBSTRING function can be used as follows:

SUBSTRING ( expression, start, length )

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.

The SUBSTRING function in MySQL database starts counting from the end of the string if you provide a negative number. However, the MS SQL Server’s SUBSTRING function does not.

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);

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:

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.