SQL Server Substring

SUBSTRING is a SQL Server built-in function that allows us to extract a specific substring from any given string set based on our requirements. Database developers widely use this function in queries to extract any number of substrings from an input string. Substring extracts a string of a specified length from an input string beginning at a specified position. This function's main objective is to return a particular portion of a string.

Syntax for Substring

The following is the basic syntax of substring function in SQL Server:

Parameters

The parameter description of this function is here:

• Expression: It's an input string from which we will extract a portion of the string (substrings) as required. It can be any character, text, ntext, binary, or image.
• Position: It's an integer value that determines the starting location from which the specific portion of a string in the given expression can be extracted. An expression's first position is always 1, not zero. It also supports negative integer value as the starting position.
• Length: It's a positive integer value that specifies how many characters we can retrieve from the given expression. If the length is negative, it returns an error. If the start + length is greater than the length of an input string, the substring will start at the beginning and end with the input string's remaining characters.

Return Types

If the input expression is one of the supported character data types, it returns character data. If it is of binary data types, this function returns binary data. We will get the extracted substring the same type as the specified expression with the exceptions mentioned in the below table:

Points to remember

The following rules must be considered for using the substring function:

• The substring function always needs all three arguments.
• Nothing is returned by the substring function if the starting position is greater than the input string's maximum number of characters.
• The total length of the string may be longer than the input string's maximum character length. In this case, the resulting substring would be the whole string from the expressions starting position to the last letter.

Supported Versions

The following SQL Server versions can use the SUBSTRING function:

• SQL Server 2019, SQL Server 2018, SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005,
• Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

Example

Let us understand how the SUBSTRING function works in SQL Server through various examples:

1. Substring function with literal strings

The following statement retrieves a portion of the string (substring) with the length of 13, which starts from the fifth character in the input string.

It will display the below output:

The following statement retrieves an entire string. It's because when the total length of the string is longer than the input string's maximum character length, the resulting substring would be the entire string from the expressions.

It will display the below output:

2. ubstring function on Tables

To understand the substring function's use on a table, we first need to create a table. The below statement creates a "persons" table in the selected database:

Next, we will insert some values into this table as follows:

We can verify the values using the SELECT statement:

It will display the below output:

This statement uses the SQL Server substring function on both the person_name and the email columns from the "persons" table. The person's name starts at the first position and returns four characters. The email starts at the fifth position and returns eight characters.

We will get the below output where we can observe that p_name and sub_mail are the substring columns:

3. Substring function with Charindex

If we want to find the domain names present in the email column using the substring function, we can execute the statement as follows:

It will display the below output where we can observe that DomainName is the substring column that returns the portion of the string after @ symbol:

In the above query, we can observe the below function:

This function finds the index position of the @ symbol in each record, adds one to the index position because the domain name starts in the next position, and then uses the LEN Function to find the email length. Finally, the substring function extracts a portion of the string and terminates when the string is finished.

It is another substring example by using the charindex where we will find the string before @ symbol in the email column:

It will display the below output where we can observe that EmailString is the substring email column that returns the portion of the string before @ symbol:

4. Substring in WHERE Clause

The substring function in SQL Server can also work with a WHERE Clause. The below statement extracts all the records from the 'persons' table whose state name ends with a:

We will get the below output where all records have state name ends with a:

5. Substring with LIKE Operator

The LIKE operator is used to restrict from returning all records from the table. This operator is being used to limit the substring function, which extracts all records whose state name ends in York:

We will get the below output where all records have state name ends with York:

6. Substring in ORDER BY Clause

The substring function in SQL Server can also work with an ORDER BY Clause. We use the substring function inside this clause for sorting the records using the substring result set. The below query will sort the data using the substring of the state column:

We will get the below output where all records have state name ends with York:

7. Substring Negative Index Example

We can also use a negative index with the SQL Server substring function, but it might not make sense. For example, we take the substring index as (-2, 3). It means the index begins with position -2 and returns 3 characters from there. The function would return characters from 1 to 3 because we don't have any records from -2 to 0 (3 records).

Executing the statement, we will see the below output: