Microsoft SQL Server 2012 Bible

String Functions

Like most modern programming languages, T-SQL includes many string-manipulation functions:

• SUBSTRING(string, starting position, length)
• STUFF(string, insertion position, delete count, string inserted)
• CHARINDEX(search string, string, starting position)
• PATINDEX(pattern, string)
• RIGHT(string, count) and Left(string, count)
• LEN(string)
• RTRIM(string) and LTrim(string)
• UPPER(string) and Lower(string):
• REPLACE(string, string):
• dbo.pTitleCase (source, search, replace)

SUBSTRING (string, starting position, length)

Substring function returns a portion of a string. The first parameter is the string; the second parameter is the beginning position of the substring to be extracted; and the third parameter is the length of the string extracted:

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 specified 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  ...