Hit enter after type your search item

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.

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.

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:


The output:

SQL SUBSTRING

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:


The result-set:

SQL SUBSTRING column

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:


The result-set:

SQL SUBSTRING table

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:


The result:

MySQL SUBSTRING negative

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:


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:


MySQL MID

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:


The resultset:

MySQL substr

You can see the same result in the above resultant table graphic.

This div height required for enabling the sticky sidebar