Microsoft® SQL Server® 2012 Bible

201

Chapter 8: Data Types, Expressions, and Scalar Functions

8

Name

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

Chain Stays

When working with string literals, it’s generally diffi cult to insert a quote into the string without ending the string and

causing a syntax error. SQL Server handles this situation by accepting two single quotes and converting them into

one single quote within the string:

‘Life’’s Great!’ is interpreted as Life’s Great!

dbo.pTitleCase (source, search, replace)

T-SQL lacks a function to convert text to title case (fi rst letter of each word in uppercase,

and the remainder in lowercase). Therefore, the following user-defi ned function accom-

plishes that task:

CREATE FUNCTION dbo.pTitleCase (

@StrIn NVARCHAR(MAX))

RETURNS NVARCHAR(MAX)

AS

BEGIN;

DECLARE

@StrOut NVARCHAR(MAX),

@CurrentPosition INT,

@NextSpace INT,

@CurrentWord NVARCHAR(MAX),

@StrLen INT,

@LastWord BIT;

SET @NextSpace = 1;

SET @CurrentPosition = 1;

SET @StrOut = '';

SET @StrLen = LEN(@StrIn);

SET @LastWord = 0;

WHILE @LastWord = 0

BEGIN;

SET @NextSpace = 

CHARINDEX(' ', @StrIn, @CurrentPosition + 1);

IF @NextSpace = 0 -- no more spaces found

BEGIN;

SET @NextSpace = @StrLen;

SET @LastWord = 1;

END;

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

http://www.it-ebooks.info