String manipulation functions

Manipulating string data is one of the most common requirements while working with SQL Server. The good news is that there are number of built-in functions that help us along the way. In this article I'd like to introduce you a few of the string functions and demonstrate a real world solution that takes advantage of them.

Perhaps the most often performed string manipulation is selecting some portion of the variable. The functions LEFT and RIGHT grab the specified number of characters from the left or right of the string, respectively. For example, the following snippet of code returns first 6 and last 4 characters of the string variable:

DECLARE @string VARCHAR(20)
SELECT @string = 'SearchDatabase.com'
SELECT LEFT(@string, 6) AS FirstSix, RIGHT(@string, 4) AS LastFour

Results:

FirstSix 	LastFour 
--------	-------- 
Search   	.com

Notice that in the example above the string variable did not have any leading or trailing spaces. What happens to the output of LEFT and RIGHT functions if we have a leading or a trailing space? Well, they'll count the space as a character, which might or might not be what you need. You can get rid of the leading and trailing spaces using functions LTRIM and RTRIM respectively, as shown below: 

DECLARE @string VARCHAR(20)
SELECT @string = '  SearchDatabase.com  '
SELECT LTRIM(@string) AS NoLeadingSpaces, RTRIM(@string) AS NoTrailingSpaces

Results:

NoLeadingSpaces      NoTrailingSpaces
---------------      ----------------
SearchDatabase.com     SearchDatabase.com

You saw that LEFT and RIGHT can help you get the leftmost and rightmost portions of the variable. What if you need to select a portion in between? The SUBSTRING function is here to help. This function accepts the name of the variable, starting position, and the number of characters to retrieve. Check out the following example:

DECLARE @string VARCHAR(20)
SELECT @string = 'SearchDatabase.com'
SELECT SUBSTRING(@string, 7, 8) AS MiddlePortion

Result:

MiddlePortion 
------------- 
Database

In the above example I knew the starting position, as well as the number of characters I wanted returned, so the task was easy. Often you won't know how many characters you need, but will know some type of a delimiter, which is the end of the portion you want returned. For instance, I could make the above example a bit more interesting if I grab the portion starting at letter "d" up to (but not including) the period. To do so, I'll have to use three more powerful functions available in Transact-SQL - CHARINDEX, LEN and REVERSE. The CHARINDEX function finds the position of a particular character, or several characters inside a string variable. CHARINDEX takes the character you wish to search for and the name of the string variable as parameters, as in CHARINDEX('.', @string). The LEN function determines the length of the string and accepts a single parameter - the name of the string variable. Finally, the REVERSE function gives you a mirror image of the string variable. Check out the following code:

DECLARE @string VARCHAR(20)
SELECT @string = 'SearchDatabase.com'
SELECT SUBSTRING(@string, CHARINDEX('d', @string), 
	(LEN(@string) - (CHARINDEX('d', @string)-1) -
	CHARINDEX('.', REVERSE(@string)) ))

Result:

-------------------- 
Database