String Manipulations with SQL Server 2000
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
String manipulations are an inherent part of any programming language. A majority of well-to-do companies collect transactional data and then want to see nicely formatted reports. Sometimes, the format of the data in the database isn't exactly "pretty"—it needs to be manipulated in some way before it is presentable to the business users. For instance, suppose you need a report of your company's employees' work schedules. If your database is normalized, your employee table probably contains the employees' last names, first names, prefixes, suffixes, and titles in separate columns, as it should. Reports, on the other hand, need to have the full name in a single field.
In data warehousing environments, you gather the data that resides in various storage systems, and give it a common shape inside the data warehouse. More often than not, you have to get the data from sources that don't have a normalized format. For instance, it is not uncommon to scrape the online reports to gather the data because that's your only source of such data.
Other times, the format of your database will be different from the format of your other data sources. For instance, if you collect data from Excel spreadsheets, they're not likely to contain normalized data. The spreadsheets usually have quarters, years, months, and weeks all in one field; whereas your normalized database have to keep such data in separate columns.
From previous examples, it should be clear that there is much need for manipulating your string data. Fortunately, Microsoft SQL Server 2000 provides a number of functions that help you along the way. This article introduces you to many string functions available in SQL Server and gives you an example of how you can apply these functions in your code.
Just like any other programming language, Transact-SQL supports retrieving portions of the string. For instance, to retrieve the first few characters from the left of the string, you use the LEFT function. The following example retrieves the first three letters of the employees' last names in the Northwind database:
SELECT LEFT(LastName, 3) AS FirstThreeLettersOfLastName FROM Employees
Results:
FirstThreeLettersOfLastName --------------------------- Buc Cal Dav Dod Ful Kin Lev Pea Suy
Similarly, the RIGHT function lets you retrieve the portion of the string starting from the right. The following example retrieves the first two characters from the employees' last names, starting from the right:
SELECT RIGHT(LastName, 2) AS LastTwoLettersOfLastName FROM Employees
Results:
LastTwoLettersOfLastName ------------------------ an an io th er ng ng ck ma
Notice that the RIGHT and LEFT functions don't check for blank characters. In other words, if your string contains a couple of leading blanks, the LEFT(string_variable, 2) will return you two blank spaces, which might not be exactly what you want. If your data needs to be left-aligned, you can use the LTRIM function, which removes the leading blanks. For instance, the following UPDATE statement will left-align (remove any number of leading blanks) the last names:
UPDATE Employees SET LastName = LTRIM(LastName)
Similarly, if your data is padded with spaces, and you don't want to see spaces in your output, you can use the RTRIM function. For instance, suppose you have a variable that's 20 characters long, but the last two characters are blank. The following queries show what happens when you run the RIGHT function on such a variable before and after removing the trailing blanks:
DECLARE @string_var VARCHAR(20) SELECT @string_var = 'my string variable ' SELECT RIGHT(@string_var, 2) AS BeforeRemovingTrailingSpaces SELECT RIGHT(RTRIM(@string_var), 2) AS AfterRemovingTrailingSpaces
Results:
BeforeRemovingTrailingSpaces ---------------------------- AfterRemovingTrailingSpaces --------------------------- le
At times, you might want to retrieve part of the string that does not necessarily start at the first character from the left or right. In such cases, the SUBSTRING function is your friend. It retrieves the portion starting at the specified character and brings back the number of characters specified; the syntax is SUBSTRING(string_variable, starting_character_number, number_of_characters_to_return). The following example will retrieve four characters from the employees' last names, starting at the third character:
SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM Employees
Results:
PortionOfLastName ----------------- chan llah voli dswo ller ng verl acoc yama
Notice that the SUBSTRING function finds the starting character by counting from the left. In other words, if you run SUBSTRING(LastName, 3, 4) against the last name of "Buchanan", you start on the third character from the left—"c".
What if you want to start from the right side, you ask? Fortunately, there is a string function called REVERSE that gives you a mirror image of the given string. Check out the mirror image of Northwind employees' last names:
SELECT REVERSE(LastName) AS MirrorImage FROM Employees
Results:
MirrorImage -------------------- nanahcuB nahallaC oilovaD htrowsdoD relluF gniK gnilreveL kcocaeP amayuS
This way, if you want to use the SUBSTRING function starting from the right, you can use the combination of the REVERSE and SUBSTRING functions, as follows:
SELECT SUBSTRING(REVERSE(LastName), 3, 4) AS PortionOfLastNameMirrorImage FROM Employees
Results:
PortionOfLastNameMirrorImage ---------------------------- nahc hall lova rows lluF iK ilre ocae ayuS
Similarly, if you want to see a mirror image of the portion, you can use REVERSE to reverse the result of the SUBSTRING, as follows:
SELECT REVERSE(SUBSTRING(LastName, 3, 4)) AS MirrorImageOfPortion FROM Employees
Results:
MirrorImageOfPortion -------------------- nahc hall ilov owsd rell gn lrev coca amay
You often need to find an occurrence of a particular character or number of characters inside a string. For example, you might want to find a position of a comma inside last names if they contain a last name and a suffix, separated by a comma.
The following example shows how this can be achieved using the CHARINDEX function:
DECLARE @string_var VARCHAR(20) SELECT @string_var = 'Brown, Jr. ' SELECT CHARINDEX( ',', @string_var) AS comma_position
Results:
comma_position -------------- 6
The PATINDEX function is very similar to CHARINDEX in the way it works—it also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX, and it searches for a pattern. If you use a % wildcard with CHARINDEX, you won't find anything unless your data contains percent signs. If you're searching for a pattern at the end of the string expression, you only have to use the % wildcard at the beginning of the pattern to be found, as in PATINDEX ('%pattern_to_find', string_expression).
An example of using PATINDEX is provided in the following code:
DECLARE @companyName VARCHAR(20), @pattern_position INT
SELECT @CompanyName = 'Green & Waldorf'
SELECT @pattern_position = PATINDEX('%Wal%', @CompanyName)
SELECT @pattern_position
Result:
----------- 9
Occasionally, you might need to replace some characters inside a string. For instance, suppose you're designing a report of employee titles, and you want to use the 'Customer Service' phrase instead of 'Sales' in titles. However, other reports still need to show the regular titles. No need to worry—the REPLACE function is here to help, as the following example demonstrates:
SELECT REPLACE(Title, 'Sales', 'Customer Service') AS ManipulatedTitle, Title FROM Employees
Results:
|
ManipulatedTitle |
Title |
|
Customer Service Representative |
Sales Representative |
|
Vice President, Customer Service |
Vice President, Sales |
|
Customer Service Representative |
Sales Representative |
|
Customer Service Representative |
Sales Representative |
|
Customer Service Manager |
Sales Manager |
|
Customer Service Representative |
Sales Representative |
|
Customer Service Representative |
Sales Representative |
|
Inside Customer Service Coordinator |
Inside Sales Coordinator |
|
Customer Service Representative |
Sales Representative |
This example was relatively simple because you knew exactly what sequence of characters you wanted to replace. What if you only know the position of the characters? Suppose that you have some clients who contain ampersands (&) in their names, and your reporting tool cannot handle special characters such as ampersands. The STUFF function can help you replace such special characters with their equivalent expressions.
You saw how to find the position of a specific character or number of characters using CHARINDEX. Now, you can apply that knowledge and use the STUFF function to replace characters based on their position.
The following example determines the position of the offending character (&) in the string variable and then replaces it with 'AND':
DECLARE @CompanyName VARCHAR(20), @amp_position INT SELECT @CompanyName = 'Green & Waldorf' SET @amp_position = CHARINDEX( '&', @CompanyName) SELECT @CompanyName = STUFF(@CompanyName, @amp_position, 1, 'AND') SELECT @CompanyName AS CompanyName
Results:
CompanyName -------------------- Green AND Waldorf
Another common need is finding the length of the character string or some portions thereof. For instance, you might have a need to replace leading spaces with zeros in some character columns. The number of zeros you need depends on how many spaces each column contains, which can vary from one row to the next. To find out how many leading spaces you have, you can use the LEN function, as the following example demonstrates:
DECLARE @AlphaCode VARCHAR(10) SELECT @AlphaCode = ' AB03543' SELECT LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode)) AS NumberOfLeadingSpaces
Results:
NumberOfLeadingSpaces --------------------- 2
Next, to replace the leading spaces, you can use the combination of the REPLACE and REPLICATE functions. You've already seen the REPLACE function in action. The REPLICATE function simply prints a character or a number of characters as many times as you specify, as follows:
SELECT REPLICATE('MyCoolString', 5)
Result:
------------------------------------------------------------ MyCoolStringMyCoolStringMyCoolStringMyCoolStringMyCoolString
To replace the leading spaces with zeros, you simply replicate the '0' string times the number of leading spaces in your column:
DECLARE @AlphaCode VARCHAR(10)
SELECT @AlphaCode = ' AB03543'
SELECT @AlphaCode = REPLICATE('0', LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode)))
+ LTRIM(@AlphaCode)
SELECT @AlphaCode AS NewAlphaCode
Result:
NewAlphaCode ------------ 00AB03543
Notice that unlike Visual Basic and some other programming languages, string concatenation in Transact-SQL is accomplished with a plus (+) sign rather than an ampersand (&).
Another specific string function you might want to be aware of is SPACE, which works exactly like REPLICATE, except it takes a single parameter. The parameter specifies how many spaces you want printed:
SELECT SPACE(12) AS Spaces
Result:
Spaces
------------
For reporting purposes, you also might have to change the case of your output. This is a simple task using the UPPER and LOWER functions. For example, the following query will return the employees' last and first names in mixed case:
SELECT UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, (LEN(FirstName) - 1))) + ' ' + UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, (LEN(LastName) - 1))) AS FullName FROM Employees
Results:
FullName --------------------------------- Nancy Davolio Andrew Fuller Janet Leverling Margaret Peacock Steven Buchanan Michael Suyama Robert King Laura Callahan Anne Dodsworth
In some cases, you might wish to see the ASCII representation of your characters. You'll use the ASCII function more often when comparing characters without knowing whether they're in upper- or lowercase. Keep in mind that uppercase and lowercase letters translate into different ASCII values, as the following example shows:
SELECT ASCII('W') AS UpperCase, ASCII('w') AS LowerCase
Results:
UpperCase LowerCase ----------- ----------- 87 119
The UNICODE function works just like ASCII, except it accepts the unicode character value as input. This can be useful if you're working with international character sets.
Another useful function is CHAR. Although it's difficult to think of a business example when you need to see some weird characters on the report, it's often necessary to append a carriage return, line feed, or both to your output. In such cases, you can effectively use the CHAR function, as follows:
SELECT 'My Output' + CHAR(10) + CHAR(13) + 'AnotherOutput'
Results:
------------------------ My Output AnotherOutput
The NCHAR function works exactly like CHAR, except it returns the unicode character.
The QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally, it's a bad idea to use reserved words, special characters, and spaces inside your object names. However, if you're working with tables imported from data sources other than SQL Server, or if you inherit a database from you predecessor colleagues, you might not have a choice. The QUOTENAME function is actually very simple—it appends square brackets to the beginning and end of the string expression, and therefore makes such an expression a valid SQL Server identifier. The following example creates a temporary table with the column name that contains spaces:
CREATE TABLE #temp (id_column INT NULL)
DECLARE @column_name VARCHAR(50), @sql_string VARCHAR(200)
SELECT @column_name = QUOTENAME('invalid column name')
SELECT @sql_string = 'ALTER TABLE #temp ADD ' + @column_name
+ 'VARCHAR(50)'
EXEC (@sql_string)
SELECT * FROM #temp
Results:
id_column invalid column name ----------- --------------------------------------------------
The STR function can be considered as a special case of the CAST or CONVERT functions, both of which let you convert the variable from one data type into another compatible datatype. As the name implies, the STR function converts an integer (or a decimal) value into a string. The nice part about the STR function is that it lets you specify the length of the string variable returned, as well as how many decimal points to include in the string variable. For instance, the following example converts a decimal value into a string expression and rounds one decimal place:
SELECT STR(1.2546, 6, 3)
Result:
------ 1.255
The other two string functions available in SQL Server are SOUNDEX and DIFFERENCE, which happen to be rather similar. I've also found them to be extremely difficult to use. SOUNDEX provides a numeric representation of the string, and is supposed to help you determine whether two strings sound alike. DIFFERENCE, on the other hand, will provide you with a degree of similarity (or lack thereof) between two character expressions. If the SOUNDEX values are the same for the two strings passed to the DIFFERENCE function, the degree of similarity is the highest: 4. Otherwise, the DIFFERENCE function will return 3, 2, 1, or 0. The DIFFERENCE function can be used when you wish to find all customers with a name that sounds similar to a known value, as in the following example:
SELECT ContactName FROM customers WHERE DIFFERENCE (ContactName, 'ana') > 2
Results:
ContactName ------------------------------ Ana Trujillo Antonio Moreno Hanna Moos Janine Labrune Ann Devon Aria Cruz Lino Rodriguez Annette Roulet John Steel Jaime Yorres Jean Fresnière Simon Crowther Rene Phillips
Summary
In this article, I gave you an introduction to all string functions available with SQL Server 2000. The next article will dissect a real-world example of applying these string functions to solve a business problem.