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

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.