SQL Server Articles

Submit Article
Home » Articles » SQL Server » String FunctionsRSS Feeds


Posted By: Sarita Patel     Category: SQL Server     Views: 90301

This article explains SUBSTRING function of sql server with examples.

SUBSTRING is used to get part of string from specified string. You can specify starting index and length of the string.   

Syntax of SUBSTRING Function :

SUBSTRING (expression ,start ,length)

expression is a string from which part of a string is returned It can be character string, binary string, text, image, a column, or a column.

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) 


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

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' 

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. 


Didn't find what you were looking for? Find more on SUBSTRING Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of SUBSTRING Function is from United States.
View All Articles

Related Articles and Code:

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!