SUBSTRING Function In MS SQL Server 2000 vs ORACLE Server

SUBSTRING (Transact-SQL) in SQL Server 2000

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

Syntax
--------
SUBSTRING (value_expression ,start_expression ,length_expression )

Arguments
---------
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 specifed in value_expression. In this case, the number of characters that are returned is the
largest value of either the sum of start_expression and length_expression 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.

Substring in sql server 2008:

1st statement:
Substring returns the part of the string from a given value expression

Test 1st statement:
select substring('This Is MS Sql Server 2008',1,0)  "Sub String"
Sub String    
----

(1 row(s) affected)

select substring('This Is MS Sql Server 2008',1,3)  "Sub String"
Sub String     
----
Thi
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',1,26)  "Sub String"
Sub String                
--------------------------
This Is MS Sql Server 2008
(1 row(s) affected)

2nd Statement from MSDN:

If start_expression is less than 1, the returned expression will begin at the first character
that is specifed in value_expression.
In this case, the number of characters that are returned is the Largest value of either the sum of start_expression and length_expression or 0.

Test 2nd statement:
select substring('This Is MS Sql Server 2008',-1,3)  "Sub String"
Sub String
----------
T
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',-1,5)  "Sub String"
Sub String
----------
Thi
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',0,5)  "Sub String"
Sub String
----------
This
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',-5,5)  "Sub String"
Sub String
----------

(1 row(s) affected)

select substring('This Is MS Sql Server 2008',-7,5)  "Sub String"
Sub String
----------

(1 row(s) affected)

Conclusion:
From the above observations the number of characters that are returned are largest value of (sum(start_expression,lemgth_expression)-1 or 0)
we need to correct statement from MSDN.
The number of characters returned when start_expression < 1 is the largest value of either (start_expression+length_expression-1) or 0

3rd Statement:

If start_expression is greater than the number of characters in the value expression,a zero-length expression is returned.

Test 3rd statement:
select substring('This Is MS Sql Server 2008',27,0)  "Sub String"
Sub String
----------

(1 row(s) affected)

select substring('This Is MS Sql Server 2008',27,1)  "Sub String"
Sub String
----------

(1 row(s) affected)

Conclusion:
If start_expression is greater than the number of characters in the value expression,substring don't think about length expression,
a zero-length expression is returned.

4th statement:

length expression is a positive integer or bigint expression that specifies how many characters of the value_expression will be returned.

Test 4th statement:
select substring('This Is MS Sql Server 2008',2,3)  "Sub String"
Sub String
----------
his
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',5,6)  "Sub String"
Sub String
----------
 Is MS
(1 row(s) affected)
In the second example 5th character is space so it starts with space.

5th statement:

If length_expression is negative, an error is generated and the statement is terminated.

Test 5th statement:
select substring('This Is MS Sql Server 2008',2,-3)  "Sub String"
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

select substring('This Is MS Sql Server 2008',5,-6)  "Sub String"
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

6th statement:

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.

Test 6th statement:
select substring('This Is MS Sql Server 2008',0,27)  "Sub String"
Sub String                
--------------------------
This Is MS Sql Server 2008
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',-1,27)  "Sub String"
Sub String                
--------------------------
This Is MS Sql Server 200
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',-1,28)  "Sub String"
Sub String                
--------------------------
This Is MS Sql Server 2008
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',1,26)  "Sub String"
Sub String                
--------------------------
This Is MS Sql Server 2008
(1 row(s) affected)

select substring('This Is MS Sql Server 2008',13,14)  "Sub String"
Sub String    
--------------
ql Server 2008
(1 row(s) affected)

SUBSTR function In Oracle:

The SUBSTR function return a portion of string, beginning at a specified position in the string.

SUBSTR calculates lengths using characters as defined by the input character set.

Return Value:

The return value is the same data type as string.

Syntax

SUBSTR (Char, position [, substring_length])

Arguments

String

A text expression that is the base string from which the substring is created.

position

The position at which the first character of the returned string begins.

When position is 0 (zero), then it is treated as 1.

Select substr ('This is oracle DB',0,7) Result_set from dual;

Result_set

This is

When position is positive, then the function counts from the beginning of string to find the first character.

select substr('this is oracle DB',3,12) Result_set from dual;

RESULT_SET

is is oracle

Select substr ('this is oracle DB',19,2) Result_set from dual;

RESULT_SET

null

When position is negative, then the function counts backward from the end of string.

Select substr('this is oracle DB',-4,5) Result_set from dual;

RESULT_SET

e DB

Select substr('this is oracle DB',-9,5) Result_set from dual;

RESULT_SET

oracl

Select substr('this is oracle DB',-17,6) Result_set from dual;

RESULT_SET

this i

Select substr('this is oracle DB',-19,6) Result_set from dual;

RESULT_SET

Null

Substring_length

The length of the returned string. SUBSTR calculates lengths using characters as defined by the input character set. 

When you do not specify a value for this argument, then the function returns all characters to the end of string. When you specify a value that is less than 1, the function returns NA.

if we specify the string length value  more than (total number of characters-starting position) then all characters from the starting position will return(starting position +ve).

select substr('this is oracle DB',2) Result_set from dual;

RESULT_SET

his is oracle DB

select substr('this is oracle DB',2,0) Result_set from dual;

RESULT_SET

null

select substr('this is oracle DB',2,-5) Result_set from dual;

RESULT_SET

null

select substr('this is oracle DB',2,19) Result_set from dual;

RESULT_SET

his is oracle DB

Comparision:

		Sql server			Oracle
		SUBSTRING (value_expression ,start_expression ,length_expression )			SUBSTR (Char, position [, substring_length])
Start Expression Less than 1		If start_expression is less than 1, the returned expression will begin at the first character that is specifed 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			start_expression=0 treated as start_expression=1 start_expression Negative Then the function counts backward from the end of string.
Positive and less than total number of characters		We can locate starting position with in the string easily			We can locate starting position with in the string easily
Positive and more than total number of characters			Zero-length expression is returned.		Zero-length expression is returned.
Negative and less than total number of characters			If start_expression is less than 1, the returned expression will begin at the first character that is specifed 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		We need to count backward from the end of string to locate starting position.
Negative and more than total number of characters			If start_expression is less than 1, the returned expression will begin at the first character that is specifed 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		Zero-length expression is returned.
Length_Expression	Sql server			Oracle
Negative	Return Error			Return Null
Zero	Return Null			Return Null
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.			The whole value expression beginning at start_expression is returned.