Thursday, September 20, 2012

NULL handling in String concatenation

            While concatenating varchar/char variable with other varchar/char variables,variable with null values assumed as empty space in sybase but not in SQL Server.In SQL Server any operations with null  results null.
            Sybase also provides the NULL result,while the integer manipulation using null value.I am giving the example to understand the scenario.
Sybase:
           SELECT @var1='Hi'
           SELECT @var2='I am'
           SELECT @var3=null
           SELECT @var1+@var2+@var3
Result:
  'Hi I am'
SQL Server:
          SELECT @var1='Hi'
          SELECT @var2='I am'
          SELECT @var3=null
          SELECT @var1+@var2+@var3
Result:
   NULL
           To avoid the data changes and maintain the integrity, we should handle the scenario.The statment should be like below.

SELECT @var1+@var2+ISNULL(@var3,'')
           For the above statement,we can get the same result of Sybase.

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

No comments:

Post a Comment