substring

Description

Returns the string formed by extracting the specified number of characters from another string.

Syntax

substring(expression, start, length)

Parameters

expression

is a binary or character column name, variable, or constant expression. Can be char, nchar, unichar, varchar, univarchar, or nvarchar data, binary, or varbinary.

start

specifies the character position at which the substring begins.

length

specifies the number of characters in the substring.

Examples

Example 1

Displays the last name and first initial of each author, for example, “Bennet A.”:

select au_lname, substring(au_fname, 1, 1) 
from authors

Example 2

Converts the author’s last name to uppercase, then displays the first three characters:

select substring(upper(au_lname), 1, 3)
from authors

Example 3

Concatenates pub_id and title_id, then displays the first six characters of the resulting string:

select substring((pub_id + title_id), 1, 6)
from titles

Example 4

Extracts the lower four digits from a binary field, where each position represents two binary digits:

select substring(xactid,5,2) 
from syslogs

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute substring.

See also

Functions charindex, patindex, stuff