Microsoft® SQL Server® 2012 Bible

202

Part II: Building Databases and Working with Data

SET @CurrentWord = 

UPPER(SUBSTRING(@StrIn, @CurrentPosition, 1));

SET @CurrentWord = @CurrentWord +

LOWER(SUBSTRING(@StrIn, @CurrentPosition+1,

@NextSpace - @CurrentPosition));

SET @StrOut = @StrOut + @CurrentWord;

SET @CurrentPosition = @NextSpace + 1;

END;

RETURN @StrOut;

END;

Running a user-defi ned function requires including the owner name in the function name:

SELECT dbo.pTitleCase('one TWO tHrEe') AS TitleCase;

Result:

TitleCase

------------------------

One Two Three

The dbo.pTitleCase function does not take into consideration surnames with nonstandard capitalization, such

as McDonald, VanCamp, or de Jonge. It would be inadequate to hard-code a list of exceptions. Perhaps the best solu-

tion is to store a list of exception phrases (Mc, Van, de, and so on) in an easily updatable list.

New SQL Server 2012 String Functions

SQL Server 2012 introduces two new string functions, CONCAT and FORMAT.

The CONCAT Function

The CONCAT function combines two or more strings into a single string:

SELECT CONCAT('Patrick ','LeBlanc') Results;

Result:

Results

---------------

Patrick LeBlanc

The function requires a minimum of two arguments. It will implicitly convert all values to

a string, and NULL values are converted to an empty string:

SELECT CONCAT(NULL, 'Patrick ', 1, ' LeBlanc') Results;

Results:

c08.indd 202c08.indd 202 7/30/2012 4:21:16 PM7/30/2012 4:21:16 PM

http://www.it-ebooks.info