Microsoft® SQL Server® 2012 Bible

198

Part II: Building Databases and Working with Data

SELECT SUBSTRING('abcdefg', 3, 2);

Result:

cd

STUFF (string, insertion position, delete count, string inserted)

The STUFF() function inserts one string into another string. The inserted string may

delete a specifi ed number of characters as it is inserted:

SELECT STUFF('abcdefg', 3, 2, '123');

Result:

ab123efg

The following code sample uses nested STUFF() functions to format a U.S. Social Security

number:

SELECT STUFF(STUFF('123456789', 4, 0, '-'), 7, 0, '-');

Result:

123-45-6789

CHARINDEX (search string, string, starting position)

CHARDINDEX returns the character position of a string within a string. The third argument

is optional and rarely used in practice. It defaults to 1.

SELECT CHARINDEX('c', 'abcdefg', 1);

Result:

3

The user-defi ned function dbo.pTitleCase() later in this section uses CHARINDEX() to

locate the spaces separating words.

PATINDEX(pattern, string)

PATINDEX searches for a pattern, which may include wildcards, within a string. The follow-

ing code locates the fi rst position of either a c or a d in the string:

SELECT PATINDEX('%[cd]%', 'abdcdefg');

Result:

3

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

http://www.it-ebooks.info