Substring in Sybase and SQL Server

             Normally Sybase and SQL Server produces the same result for Substring() Function .But in some extends which produces the different results.
To Explain the substring behavior,i chosen four cases.
CASE 1:
         Declare @var varchar(30)='hi'         

         SELECT SUBSTRING(@var,start position = 0, length = 0)
Sybase result          =NULL
SQL Server result  =''
Both results are different.we need to handle this case.

CASE 2:
         Declare @var varchar(30)='hi'
         SELECT SUBSTRING(@var,start position=0, length = 1)
Sybase result          =NULL
SQL Server result  =''
Both results are different.we need to handle this case.

CASE 3:
         Declare @var varchar(30)='hi'
         SELECT SUBSTRING(@var,start position=1, length = 0)
Sybase result          =NULL
SQL Server result  =''
Both results are different.we need to handle this case.

CASE 4:
         Declare @var varchar(30)='hi'
         SELECT SUBSTRING(@var,start position=1, length = 1)
Sybase result          =h
SQL Server result  =h
Both results are same.We don't need to do any change.
If the starting position and legnth is more than 1,both the technologies will give the same results.
In that case we dont need to handle.
How to get Sybase result from SQL Server:
        To get the same result of Sybase, we should handle the first three cases.If we make the common code change like below,We can attain same result of Sybase.

         SELECT CASE WHEN SUBSTRING(@var,start position=0, length = 0)=''
                                   THEN NULL
                                   ELSE SELECT SUBSTRING(@var,start position=0, length = 0)
                        END
         By this code change,we can avoid the result of empty space ('').Using the SSMA generated  Sysdb Function,we can get the same result.Sample SSMA generated functions are given below.
SYSDB.SSMA_SYB.SUBSTRING_NVARCHAR(expression, start, length)
SYSDB.SSMA_SYB.SUBSTRING_VARCHAR(expression, start, length)

Please click here to know about sybase to sql server migration issues