start is an integer of starting position from where part of a string begins.
length is number of characters to be returned from specified string. It gives error if you specify negative value as length.
Return type of SUBSTRING is based on expression you specify in function. It can be character, binary, text or image.
Examples of SUBSTRING Function :
Example 1 : Use of SUBSTRING function in select clause
SELECT SUBSTRING('Syntax-Example', 8,7)
Output
Example
Above example returns 7 characters starting from 8th character of a string i.e. 'Example'.
Example 2 : Use of SUBSTRING function to display field value of table in a select clause
SELECT ContactName, SUBSTRING(ContactName, 1, 4) AS 'First Part of Name'
FROM Customers
Output
ContactName First Part of Name
Maria Anders Mari
Ana Trujillo Ana
Antonio Moreno Anto
Thomas Hardy Thom
Christina Berglund Chri
Hanna Moos Hann
Above example displays first 4 characters of customer name from customers table as starting index parameter is 1 and number of characters parameter is 4.
Example 3 : Use of SUBSTRING function in where clause
SELECT ContactName
FROM Customers
WHERE SUBSTRING(ContactName, LEN(ContactName)-1,2) = 'on'
Output
Patricio Simpson
Ann Devon
Maria Larsson
Fran Wilson
Paula Wilson
Liz Nixon
Above example displays all customer name having last 2 characters as 'on'. Starting index calculated by subtracting 1 from length of contact name.