none
different behavior in sql and sybase of an statment RRS feed

  • Question

  • Following SQL Statement used in many procedures on SQLServer is returing one * however the same is returning Two ** on Sybase. 

    Statement :
    select isnull(nullif(rtrim(NULL), ' '), '**') 

    SYBASE:
    SELECT  isnull(rtrim(program_name),'**') 
    FROM master..sysprocesses --which returns 2 ** 


    SQLSERVER: 
    SELECT  isnull(nullif(rtrim(master.dbo.sysprocesses.program_name), ''), '**') 
    FROM master..sysprocesses --which returns 1 *. 


    SQL Server DBA

    Tuesday, October 4, 2016 7:29 AM

Answers

All replies

  • ISNULL uses the datatype and length of the parameter.

    SELECT ISNULL(NULLIF(CONVERT(varchar(10),RTRIM(NULL)), ' '), '**')


    Please click Mark As Answer if my post helped.
    Thanks,
    Senthill

    Tuesday, October 4, 2016 7:38 AM
  • When i am passing a variable in place of null in NULLIF function below statment returns according to variabe length 

    declare @var varchar(5)
    set @var = null

    select nullif(rtrim(@var), ' ')--> NULL

    select isnull(nullif(rtrim(@var), ' '), '**') -->** 

    When i am passing a NULL in NULLIF function below statment returns only length upto 1 character.

    select nullif(rtrim(NULL), ' ')--> NULL

    select isnull(nullif(rtrim(NULL), ' '), '**') -->*

    can please explian this behavior


    SQL Server DBA

    Tuesday, October 4, 2016 9:54 AM
  • We cannot determine the length and characteristics of NULL value. It is a unknown value. So this may be the feature provided by SQL server. If you want to display ** , have to convert this as you mentioned.

    Please click Mark As Answer if my post helped.
    Thanks,
    Senthill

    Tuesday, October 4, 2016 3:59 PM