Substring() in SQL Server: How to Use with Example

What is Substring()?

SUBSTRING() is a function in SQL that lets the user derive a substring from any given string as per need. SUBSTRING() extracts a string of a specified length, starting from a given location in an input string. The purpose of SUBSTRING() in SQL is to return a specific portion of the string.

Syntax for Substring()

SUBSTRING(Expression, Starting Position, Total Length)

Here:

• The SUBSTRING() Expression in SQL Server can be any character, binary, text, or image. The Expression is the source string from which the substring is fetched.
• Starting Position determines the position in the Expression from where the new substring should start.
• Total Length is the total expected length of the resulting substring from the Expression, starting from the Starting Position.

Rules for using SUBSTRING()

• All three arguments are mandatory in the MS SQL SUBSTRING() function.
• If the Starting Position is greater than the maximum number of characters in the Expression, then nothing is returned by the SUBSTRING() function in SQL Server.
• The Total Length can exceed the maximum character length of the original string. In this case, the resulting substring is the entire string, starting from the Starting Position in the Expression until the last character of the Expression.

The diagram below illustrates the use of the SUBSTRING() function in SQL Server:

T-SQL Substring Examples

Assumption: Assume that we have a table named ‘Guru99’ with two columns and four rows, as displayed below. We will use this ‘Guru99’ table in the following examples:

Query 1: SUBSTRING() in SQL with a length less than the total maximum length of the expression.

SELECT Tutorial_name, SUBSTRING(Tutorial_name,1,2) As SUB from Guru99;

Result: The diagram below displays the substring of the ‘Tutorial_name’ column as the ‘SUB’ column. The starting position is 1 and the length is 2, so the first two characters are returned:

Query 2: SUBSTRING() in SQL Server with a length greater than the total maximum length of the expression.

SELECT Tutorial_name, SUBSTRING(Tutorial_name,2,8) As SUB from Guru99;

Result: The diagram below displays the substring of the ‘Tutorial_name’ column as the ‘SUB’ column. Even though the substring length is greater than the total maximum length of the expression, no error is raised and the query returns the full string from the starting position onward:

SUBSTRING with CHARINDEX in SQL Server

A very common real-world use of SUBSTRING() is to extract text that sits before or after a delimiter, such as the domain in an email address. On its own, SUBSTRING() needs a fixed starting position, but the position of a delimiter varies from row to row. The CHARINDEX() function solves this by returning the position of a character inside a string:

CHARINDEX(substring_to_find, expression [, start_location])

By nesting CHARINDEX() inside SUBSTRING(), the starting position becomes dynamic. The example below finds the @ symbol and returns everything after it. A variable holds the sample value:

DECLARE @Email VARCHAR(50) = 'john.doe@guru99.com';
SELECT SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email)) AS Domain;

Here, CHARINDEX(‘@’, @Email) locates the position of the @ symbol, adding 1 moves past it, and SUBSTRING() then extracts the remaining characters. For the value above, the query returns the domain guru99.com. This CHARINDEX-plus-SUBSTRING pattern is the standard way to parse structured strings in T-SQL.

SUBSTRING vs LEFT and RIGHT in SQL Server

SQL Server also provides the LEFT() and RIGHT() functions to pull characters from the start or end of a string. They are shorter to write, but they are limited to the two ends. SUBSTRING() is the most flexible because it can start at any position. The table below compares them:

In short, LEFT() and RIGHT() are convenient shortcuts for the ends of a string, while SUBSTRING() handles the general case, including characters taken from the middle.

Negative, Zero, and NULL Arguments in SUBSTRING

Beyond the basic rules, it helps to know how SUBSTRING() behaves at the edges. When the starting position is zero or negative, SQL Server computes an effective length of (start + length – 1) and begins reading from position one. When any argument is NULL, the result is NULL. The table below, verified against the official SUBSTRING (Transact-SQL) reference, shows these cases:

Knowing these edge cases prevents surprises when the starting position is calculated from another column or a variable that could be zero or NULL.

FAQs