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
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
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
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)) ))
To return the word database I grabbed the portion of the string variable starting at letter 'd'. Then I subtracted one from the position of the letter 'd', because I'd like to keep the 'd' in the output. Finally, I needed to know the position of the period, counting from the right. I used the REVERSE function in conjunction with CHARINDEX to do that. The result is the portion of the string from the letter 'd' to the period.
You also often have a need to return certain characters in upper or lowercase, without affecting the underlying data. The functions UPPER and LOWER return the uppercase and lowercase versions of the string, respectively.
Now let's take these functions and apply it to a somewhat more complicated example. Suppose you want to uppercase all words in a string. This is a common requirement for reports, where all words involved in individual's or company names must be capitalized. The snippet of code below will find all spaces in the string and capitalize each first letter after the space. To be consistent, I'll capitalize the last word in the string, which has no space following it:
SET CONCAT_NULL_YIELDS_NULL OFF /* declare the variables to hold the old and new company names */ DECLARE @comp_name VARCHAR(75), @newcomp_name VARCHAR(75) SELECT @comp_name = ' woderful world, inc. ' -- upper case each word after a space SELECT @comp_name = LTRIM(RTRIM(@comp_name)) BEGIN WHILE CHARINDEX(' ', @comp_name)<> 0 BEGIN SELECT @newComp_name = @newComp_name + UPPER(LEFT(@comp_name, 1)) + LOWER(SUBSTRING(@Comp_name, 2, (CHARINDEX(' ', @Comp_name)-1))) SELECT @comp_name = SUBSTRING(@comp_name, (CHARINDEX(' ', @comp_name) + 1), (LEN(@comp_name) - CHARINDEX(' ', @comp_name))) END -- Add the last piece (not containing spaces): SELECT @NewComp_Name = @NewComp_Name + UPPER(LEFT(@comp_name, 1)) + LOWER(SUBSTRING(@comp_name, 2, LEN(@comp_name) - 1)) END SELECT @NewComp_Name AS UpperCasedName
UpperCasedName ------------------------ Woderful World, Inc.
So in this article I introduced you to some of the powerful string manipulation techniques using the built-in functions of Microsoft SQL Server 2000.
For More Information
- What do you think about this tip? E-mail us at firstname.lastname@example.org with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2001