O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required