The use of SUBSTRING functions in SQL

function: returns part of a character, binary, text, or image expression

Syntax: SUBSTRING (expression, start, length)

the substring function in SQL is used to grab a portion of a field data. The name of this function is not exactly the same in different repositories:

• Mysql:substr (), SUBSTRING ()
• Oracle:substr ()
• SQL server:substring ()

Parameters:

An expression string, binary string, text, image, column, or expressions that contain columns. Do not use an expression that contains an aggregate function.

Start integer or an expression that can be implicitly converted to int, specifying the starting position of the substring.

A length integer or an expression that can be implicitly converted to an int, specifying the substring's lengths.

return value:

If expression is a supported character data type, character data is returned. If expression is a supported binary data type, then binary data is returned. If start = 1, the substring starts with the first character of the expression.

The type of the returned string is the same as the type of the given expression (except as shown in the following table).

given an expression	return type
Image	varbinary
ntext	nvarchar

code example:

The following example returns the initials and full last name of each employee in the Employees table:

SELECT SUBSTRING (first name,1,1) as Initial, last Name
From Employees

Here is the result set:

Initial ..... Last Name
-------------------------
A................ Funk
M................ Pearson
L .......... Calafato
N................ Danner
J................ Lee
S ....... ..... Byham
M................ Sutter
R................ King
A................ Doyle

The use of SUBSTRING functions in SQL