SQL SUBSTRING Function (Transact SQL)

Description

The SQL SUBSTRING function extracts a part of input expression or a ‘substring’ and returns it. The starting position and the substring’s length are passed as arguments of the SUBSTRING function.

The SUBSTRING function is used to return characters from within another string.

The general form of the SUBSTRING function is

SUBSTRING(value, position, length)

Where value is the string you’re working with, position is character to start returning characters, and length is the number of characters to return.

If length is negative an error is returned.

If either position or length are NULL, then NULL is returned.

For example,

SUBSTRING(‘SQL Server’,5,3)

returns ‘Ser’.  It the sequence of three characters starting in the fifth position.

SUBSTRING is a generalized form of the LEFT and RIGHT functions.

Extra Credit – Show how LEFT and RIGHT can be used together to simulate SUBSTRING.  Show your answer in the comments below.

LEFT(‘SQL Server’,4) and SUBSTRING(‘SQL Server’,1,4) both return the beginning four characters.

RIGHT is a little more complicated!

RIGHT(‘SQL Server’,4) can be written as SUBSTRING(‘SQL Server’,7,4)

Which says to start at the 7^th position and then return the next four characters.  The general form for this, since you won’t always know the length of the value, is

SUBSTRING(‘SQL Server’, LEN(‘SQL Server’)-4, 4)

Here is an example using SQL columns.

SELECT LEFT(Name,2) as Left2,
       SUBSTRING(Name, 1, 2) as Substring2,
       RIGHT(Name,3) as Right3,
       SUBSTRING(Name, LEN(Name)-2,3) as Substring3
FROM   Production.Product

Whose results are

SQL SUBSTRING Usage Notes

1. The input expression as the first argument of the SQL SUBSTRING function can be a character, text, binary value, or an image. 
2. Start, as the second argument of the SUBSTRING function, indicates the index or position to start extracting the substring. If the start value exceeds the length of the input expression, the SUBSTRING returns a blank substring of zero length. If the start value is negative, the function computes the value of start+length-1. In the case the value is less than zero, the function returns an empty string. Otherwise, the SUBSTRING function returns characters from the input expression equal to the value of start+length-1.
3. Length as the final argument of the SQL SUBSTRING function indicates the number of characters in the input expression that return as an output. It is a positive integer value. In the case of a negative value, an error returns. If the length of the input expression beginning from the start position is less than the length argument, then the whole input expression from the start position returns.
4. The output data type is varchar for varchar, char, and text input expression. For binary, varbinary, and image input, the return data type is varbinary.

Syntax

SUBSTRING ( input_expression ,start , length ) 

SQL SUBSTRING Examples

We will use the AdventureWorks2019 database for all the examples.

The following example shows how a simple SQL SUBSTRING function works.

/* Answer */
SELECT SUBSTRING('substring', 1, 3) outputString; 

The query extracts three characters beginning from position one and returns them as an output string.

Next, let’s see what the output string is if the start value in SUBSTRING is negative.

/* Answer */
SELECT SUBSTRING('substring', -1, 3) outputString;

The function computes the value of start+length-1. -1+3-1 = 1 and is greater than zero, SQL SUBSTRING extracts one character from the start of the input string. The output is s.

If the start+length-1 is zero or less than zero, an empty string returns, as shown in the example below.

/* Answer */
SELECT SUBSTRING('substring', -3, 3) outputString; 

The query returns a blank string as the value of -3+3-1 is -1.

Now, let’s apply the SQL SUBSTRING function to a database column.

/* Answer */
SELECT SUBSTRING(PhoneNumber, 1, 3) AS areaCode  
FROM   Person.PersonPhone
ORDER BY phonenumber DESC

The above query extracts the first three digits from each value in the phone number column of a PersonPhone table. These values return as an area code of each phone number.

For an image input expression, we get a return type of varbinary. In the following example, we extract a substring from the values of the LargePhoto column. The data type of LargePhoto is varbinary(MAX).

/* Answer */
SELECT SUBSTRING(LargePhoto, 1, 10) AS photoOutput  
FROM Production.ProductPhoto 

The query extracts the first ten values from each image binary value and returns them as a photo output.

See Also

• Introduction to SQL Server’s String Functions
• SQL STUFF
• Recommended book: SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach