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.

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.

SELECT SUBSTRING('substring', 1, 3) outputString;
/* 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.

SELECT SUBSTRING('substring', -1, 3) outputString;
/* 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.

SELECT SUBSTRING('substring', -3, 3) outputString;
/* 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.

SELECT SUBSTRING(PhoneNumber, 1, 3) AS areaCode FROM Person.PersonPhone ORDER BY phonenumber DESC
/* 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).

SELECT SUBSTRING(LargePhoto, 1, 10) AS photoOutput FROM Production.ProductPhoto
/* Answer */
SELECT SUBSTRING(LargePhoto, 1, 10) AS photoOutput  
FROM Production.ProductPhoto 
SQL Substring with Varbinary Type

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

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>