Sunday, November 30, 2008

Oracle - Substr function

Substr function in Oracle

This function helps in retrieving a part of string. It is very flexible such that we can retrieve any part of the string from any index and length.

Syntax :-
Substr(String,From_Index,[Length])

String --- Indicates the main string from which user is willing to retrieve a part or substring.
From_Index --- Indicates the character position or index from where user tries to take substring. If this value is negative, then sub string from index will be calculated from back else front or front to back.

Length --- This is optional parameter. This indicates the number of characters from the From_Index to be considered for building sub string.
By default , Substr(String,From_Index) build sub string from From_Index to end of the string.
If length is specified as ‘0’, then substr will always return null.













Example :-
InputOutput
Substr(‘srinivas’,2,0)null
Substr(‘srinivas’,2)rinivas
Substr(‘srinivas’,-2)as
Substr(‘srinivas’,2,3)rin
Substr(‘srinivas’,-2,1) a

No comments: