Problem

You have a set of string values that you would like to concatenate into one string value. This is often a requirement when formatting names or addresses. In the database the name may be stored as separate first, middle and last names; however, you may wish to execute a query that returns “Last Name, First Name” and add the middle initial if it exists.

Solution

For this example, create a FullName column from the FirstName, MiddleName and LastName columns of the Person.Person table.

SELECT TOP 5
   FullName = CONCAT(LastName, ', ', FirstName, ' ', MiddleName)
FROM Person.Person p ;

The result of this query is:

FullName
-----------------------------------
Abbas, Syed E
Abel, Catherine R.
Abercrombie, Kim
Abercrombie, Kim
Abercrombie, Kim B

How it Works

The CONCAT function accepts a variable list of string values (at least two are required) and concatenates them into one string. A difference between the CONCAT function and using the + operator is how nulls are handled. The operator + will return NULL if either the left or right side of the operator is NULL. The CONCATfunction will convert NULL arguments to an empty string prior to the concatenation.

Take the following select statement that concatenates a FullName with three different approaches:

SELECT TOP 5
   FullName = CONCAT(LastName, ', ', FirstName, ' ', MiddleName),
                FullName2 = LastName + ', ' + FirstName + ' ' + MiddleName,
                FullName3 = LastName + ', ' + FirstName +
                   IIF(MiddleName IS NULL, '', ' ' + MiddleName)
FROM Person.Person p
WHERE MiddleName IS NULL ;

This query yields the following results:

The FullName Column used the CONCAT function as seen in the recipe. FullName2 uses the + operator. The + operator will always return NULL if one of its operands is NULL – because MiddleName is NULL for all rows then FullName2 is NULLfor all rows. Finally, the FullName3 column shows the logic that is encapsulated in the CONCATfunction. In this recipe’s example, 3 columns and two string literals are concatenated together using the CONCAT function. The MiddleName column is NULL for some rows in the table but no additional NULL handling logic is required when using CONCAT to generate the FullName string.

Problem

Your application requires the ASCII values of a string’s characters or passes you ASCII values that you must assemble into a string.

Solution

This first example demonstrates how to convert characters into the integer ASCII value:

SELECT ASCII('H'),
   ASCII('e'),
   ASCII('l'),
   ASCII('l'),
   ASCII('o') ;

This returns:

72	101	108	108	111

Next, the CHAR function is used to convert the integer values back into characters again:

SELECT CHAR(72),
   CHAR(101),
   CHAR(108),
   CHAR(108),
   CHAR(111) ;

This returns:

H e l l o

How it Works

The ASCII function takes the leftmost character of a character expression and returns the ASCII code. The CHAR function converts the integer value of an ASCII code to a character value. The ASCII function only converts the first character of the supplied string. If the string is empty or NULL, ASCII will return NULL (although a blank, single-space value is represented by a value of 32).

In this recipe, the word “Hello” is deconstructed into 5 characters and then converted into the numeric ASCII values using the ASCII function. In the second T-SQL statement the processis reversed and the ASCII values are converted back into characters using the CHAR function.

Problem

Your application requires the Unicode values of a string’s characters or passes you Unicode values that you must assemble into a string.

Solution

The UNICODE function returns the Unicode integer value for the first character of the character or input expression. The NCHAR function takes an integer value designating a Unicode character and converts it to its character equivalent.

This first example converts single characters into an integer value representing the Unicode standard character code:

SELECT UNICODE('G'),
   UNICODE('o'),
   UNICODE('o'),
   UNICODE('d'),
   UNICODE('!') ;

This returns:

71 111 111 100 33

Next, the Unicode integer values are converted back into characters:

SELECT NCHAR(71),
   NCHAR(111),
   NCHAR(111),
   NCHAR(100),
   NCHAR(33) ;

This returns

G o o d !

How it Works

In this example, the string “Good!” is deconstructed one character at a time and then each character is converted into an integer value using the UNICODE function. In the second example, the integer values are reversed back into characters by using the NCHAR function.

Problem

You need to find out where a string segment or character pattern starts within the context of a larger string. For example, you need to find all of the street addresses that match a pattern you are looking for.

Solution

This example demonstrates how to find the starting position of a string within another string:

SELECT CHARINDEX('string to find','This is the bigger string to find something in.'),

This returns

20

That is, the first character of the first instance of the string “string to find” is the 20^th character of the string that we are searching.

In some cases a character pattern must be found within a string. The following example returns all rows from the address table that contain the digit 0 preceding the word “Olive”

SELECT TOP 10
   AddressID,
   AddressLine1,
   PATINDEX('%[0]%Olive%', AddressLine1)
FROM Person.Address
WHERE PATINDEX('%[0]%Olive%', AddressLine1) > 0 ;

The results of this statement:

Problem

You need to replace all instances of a string value within a larger string value. For example, the name of a product has changed and you must update product descriptions with the new product name.

Solution

This example replaces all instances of the string “Classic” with the work “Vintage”:

SELECT REPLACE('The Classic Roadie is a stunning example of the bikes that AdventureWorks have been producing for years – Order your classic Roadie today and experience AdventureWorks history.', 'Classic', 'Vintage'),

This returns:

The Vintage Roadie is a stunning example of the bikes that AdventureWorks have been producing for years – Order your Vintage Roadie today and experience AdventureWorks history.

How it Works

The REPLACE function searches a source string for all instances of a provided search pattern and replaces them with the supplied replacement string. A strength of REPLACE is that unlike PATINDEX and CHARINDEX that return one location where a pattern is found, REPLACE finds and replaces all instances of the search string within a specific character string. The syntax for REPLACE is as follows:

REPLACE ( string_expression , search_string , replacement_string );

The first argument, string_expression, is the string that will be searched. The second argument, search_string, is the string to be removed from the original string. The third argument, replacement_string, is the string to use as a replacement to the search string.

In this example we searched the product description for all instances of the string “Classic” and replaced them with the string “Vintage”.

REPLACE can also be used to remove portions of a string. If the replacement_string parameter is an empty string (''), REPLACE will remove search_string from string_expression and replace it with 0 characters. Note, in this case this is an empty string ('') not a NULL value. If replacement_string is NULL the the output of REPLACE will always be NULL.

Problem

You need to insert a string into another string.

Solution

This example replaces a part of a string and inserts a new expression into the string body:

SELECT STUFF ( 'My cat''s name is X. Have you met him?', 18, 1, 'Edgar' );

This returns:

My cat's name is Edgar. Have you met him?

How it Works

The STUFF function deletes a specified length of characters and inserts a designated string at the specified starting point. The syntax is as follows:

STUFF ( character_expression, start, length, character_expression )

The first argument of this function is the character expression to be modified. The second argument is the starting position of the string to be inserted. The third argument is the number of characters to delete within the string in the first argument. The fourth argument is the actual character expression that you want to insert.

The first character expression in this recipe is “My cat’s name is X. Have you met him?”. The start value is 18, meaning the replacement will occur at the 18th character in the string (“X”). The length parameter is 1 meaning only one character at position 18 will be deleted. The last character expression is Edgar. This is the value to stuff into the string.

If a 0 length parameter is specified the STUFF function simply inserts the second string into the first string before the character specified with the start argument. For example:

SELECT STUFF ( 'My cat''s name is X. Have you met him?', 18, 0, 'Edgar' );

This returns:

My cat's name is EdgarX. Have you met him?

If an empty string (‘’) is specified for the second character expression the STUFF function deletes the characters starting with the character specified with the start argument and continuing for the number of characters specified in the length argument. For example:

SELECT STUFF ( 'My cat''s name is X. Have you met him?', 18, 8, '' );

This returns:

My cat's name is you met him?

Problem

You have some text that, for reporting purposes, you would like to return as all upper case or all lower case.

Solution

The following query shows the value of DocumentSummary for a specific row in the Production.Document table:

SELECT DocumentSummary
FROM Production.Document
WHERE FileName = 'Installing Replacement Pedals.doc';

This returns the following sentence-case value:

DocumentSummary
------------------------------------ Detailed instructions for replacing pedals with Adventure Works Cycles replacement pedals. Instructions are applicable to all Adventure Works Cycles bicycle models and replacement pedals. Use only Adventure Works Cycles parts when replacing worn or broken components.

This first example demonstrates setting values to lowercase:

SELECT LOWER(DocumentSummary)
FROM Production.Document
WHERE FileName = 'Installing Replacement Pedals.doc';

This returns:

detailed instructions for replacing pedals with adventure works cycles replacement pedals. instructions are applicable to all adventure works cycles bicycle models and replacement pedals. use only adventure works cycles parts when replacing worn or broken components.

Now for uppercase:

SELECT UPPER(DocumentSummary)
FROM Production.Document
WHERE FileName = 'Installing Replacement Pedals.doc';

This returns:

DETAILED INSTRUCTIONS FOR REPLACING PEDALS WITH ADVENTURE WORKS CYCLES REPLACEMENT PEDALS. INSTRUCTIONS ARE APPLICABLE TO ALL ADVENTURE WORKS CYCLES BICYCLE MODELS AND REPLACEMENT PEDALS. USE ONLY ADVENTURE WORKS CYCLES PARTS WHEN REPLACING WORN OR BROKEN COMPONENTS.

How it Works

The LOWER function returns a character expression in lowercase, and the UPPER function returns a character expression in uppercase. If a character in the string is not case-convertible the character is returned with no conversion. For example, look at the string with Thai characters used earlier in this chapter:

SELECT UPPER (N'เป็นสายอักขระ unicode'),

This returns:

เป็นสายอักขระ UNICODE

Because there is no upper and lower case distinction for the Thai characters, UPPER and LOWER produce no affect on them.

The first example demonstrates the LOWER function and returns a character expression in lowercase. The second example demonstrates the UPPER function and returns a character expression in uppercase. In both cases the function takes a single argument: the character expression containing the case to be converted to either upper- or lowercase.

Problem

You have text entered through an application that may contain leading or trailing blanks and you would like to remove these blanks before storing the data.

Solution

This first example demonstrates removing leading blanks from a string:

SELECT CONCAT('''', LTRIM(' String with leading and trailing blanks. '), '''' );

This returns:

'String with leading and trailing blanks. '

This second example demonstrates removing trailing blanks from a string:

SELECT CONCAT('''', RTRIM(' String with leading and trailing blanks. '), '''' );

This returns:

' String with leading and trailing blanks.'

The final example shows that LTRIM and RTRIM may be used together to remove blanks from both ends of a string

SELECT CONCAT('''', LTRIM(RTRIM(' String with leading and trailing blanks ')), '''' );

This returns:

'String with leading and trailing blanks'

How it Works

Both LTRIM and RTRIM take a single argument—a character expression that is to be trimmed. The function then trims the leading or trailing blanks. Note that there is not a TRIM function (as seen in other programming languages) that can be used to remove both leading and trailing characters. To do this, you must use both LTRIM and RTRIM in the same expression.

Problem

Often when testing an application’s user interface you will need to populate sample data into a database that fills the database columns to the maximum length of character data to ensure that the UI will properly display larger strings. Generally the character ‘W’ is used as it is a wide character.

Solution

Use the replicate function to produce a string of 30 character W’s.

SELECT REPLICATE ('W', 30) ;

This returns

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWW

Use the replicate function to produce a string of 30 repetitions of the string ‘Z_’.

SELECT REPLICATE ('W_', 30) ;

This returns:

W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_W_

How it Works

The REPLICATE function repeats a given character expression a designated number of times. The syntax is as follows:

REPLICATE ( character_expression,integer_expression )

The first argument is the character expression to be repeated. The second argument is the integer value representing the number of times the character expression is to be repeated.

In this recipe’s first example the letter ‘W’ is supplied as the character expression and is as repeated 30 times. The second example shows that REPLICATE can repeat string values and not only single characers. Use REPLICATE to repeat values rather than having to enter the string literals manually.

Problem

You are formatting a set of values for display and you would like the values to be returned as a one column result set and aligned in 20 character columns.

Solution

This example demonstrates how to repeat a blank space a defined number of times to align the values onto 20-character boundaries:

DECLARE @string1 NVARCHAR(20) = 'elephant',
    @string2 NVARCHAR(20) = 'dog',
    @string3 NVARCHAR(20) = 'giraffe' ;
SELECT *
FROM ( VALUES
    ( CONCAT(@string1, SPACE(20 - LEN(@string1)), @string2,
    SPACE(20 - LEN(@string2)), @string3,
    SPACE(20 - LEN(@string3))))
 ,
    ( CONCAT(@string2, SPACE(20 - LEN(@string2)), @string3,
    SPACE(20 - LEN(@string3)), @string1,
    SPACE(20 - LEN(@string1)))) ) AS a (formatted_string) ;

This returns:

formatted_string
-------------------------------------elephant dog giraffe
dog giraffe elephant

How it Works

The SPACE function returns a string of repeated blank spaces, based on the integer you designate for the input parameter. This is the same functionality as the REPLICATE function only the character to replicate is a constant.

In this recipe there are values that should be returned in one column of text aligned to 20 character boundaries. Each values is concatenated with a number of spaces equal to 20 – the length of the string.

The maximum return value for the SPACE function is 8,000 bytes.

Problem

You have the fully qualified file name and would like to split the string into path and filename.

Solution

In this example the files in the current database are broken out into paths and filenames. Find the last backslash (‘’) character in the string and use that position as the basis for the boundary between path and filename.

SELECT Path = LEFT(filename, LEN(filename) - CHARINDEX('', REVERSE(filename)) + 1),
   FileName = RIGHT(filename, CHARINDEX('', REVERSE(filename)) - 1)
FROM sys.sysfiles ;

This example returns the following results (In this example the paths and filenames will differ depending on the database file names and locations.)

Path	FileName
----------------	---------------------------
E:SqlDatabases	AdventureWorks2012_Data.mdf
E:SqlDatabases	AdventureWorks2012_log.ldf

How it Works

The REVERSE function takes a character expression and outputs the expression with each character position displayed in reverse order.

By using CHARINDEX on the reversed string, instead of finding the first occurrence of the character the last occurrence is returned. LEFT and RIGHT are used to split the string at the identified location.