String Manipulation On Query SQL Server

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