Home / Software / Tips and Tricks / SUBSTRING function in SQL

SUBSTRING function in SQL

Updated:  10/27/2011 07:10 AM
Author:  Shiju Mathews

Status:    Resolved.


Returns part of a character, binary, text, or image expression.

Syntax:
    SUBSTRING ( value_expression , start_expression , length_expression )


value_expression:
   Is a character, binary, text, ntext, or image expression.

start_expression:
   Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specified in value_expression. In this case, the number of characters that are returned is the largest value of either the sum of start_expression + length_expression – 1 or 0. If start_expression is greater than the number of characters in the value expression, a zero-length expression is returned.

length_expression:
   Is a positive integer or bigint expression that specifies how many characters of the value_expression will be returned. If length_expression is negative, an error is generated and the statement is terminated. If the sum of start_expression and length_expression is greater than the number of characters in value_expression, the whole value expression beginning at start_expression is returned.


Note: It is a common mistake that, the developer will forget to consider the start-Expression is not zero based.