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.
Left
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'Right
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'SubString
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