This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Substring Functions

The forty-fourth part of the SQL Server Programming Fundamentals tutorial begins a look at the string processing functions provided by Transact-SQL (T-SQL). This article describes three functions that allow you to extract substrings from character types.

String Functions

In this and the next six articles, I will describe the string functions provided by Transact-SQL (T-SQL) in SQL Server 2005. These have been grouped into the following categories:

In this article we will concentrate on substring functions. These are functions that extract a group of consecutive characters from a string.


The first of the three functions that we will consider in this article is Left. This function returns a number of characters from the start of a string. The first parameter for the function is the string to be processed. This can be of any character data type that can be converted to a VarChar or NVarChar type, except for Text or NText. Other data types must be converted to one a compatible type. The second argument is an integer that specifies the number of characters that should be retrieved from the left of the string. The returned string is an NVarChar when the input value is a Unicode string and a VarChar otherwise.

We can demonstrate the use of the Left function by printing its output in a query window:

PRINT left('BlackWasp', 5)  -- 'Black'


The Right function is similar in operation to Left. Instead of retrieving a number of characters from the left side of the input string, it returns them from the right.

PRINT right('BlackWasp', 4) -- 'Wasp'


The SubString function is more powerful than the two previous functions. It allows you to extract a series of characters from any point within a character data type, including from Text and nText data. In addition, SubString can extract a group of concurrent bytes from Binary or Image information. When working with character types, SubString returns a VarChar or NVarChar. For binary types, the function returns a VarBinary result.

The SubString function requires three arguments. The first is the information from which you wish to extract some characters or bytes. The second parameter is used to specify a starting position for the extraction. If this value is one, the first character of the source string will be the first character of the result. The third argument is used to determine how many characters or bytes will be included in the result.

For example, to extract five characters from a string, starting from the third character, you could use the following:

PRINT substring('BlackWasp', 3, 5)  -- 'ackWa'
17 November 2009