Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Strange behavior with varchar comparation

Answered Strange behavior with varchar comparation

  • Friday, November 23, 2012 2:39 PM
     
      Has Code
    Sign In to Vote
    0

    Following query:

    declare @v1 varchar(10) = 'text'

declare @v2 varchar(10) = null

select case when @v1 <> @v2 then 'DIFF' else 'NO DIFF' end

    Returns a result: NO DIFF! What should I set/configure to get DIFF as a result?

     

All Replies

  • Friday, November 23, 2012 2:43 PM
     
      Has Code
    Sign In to Vote
    1

    You cannot compare NULL with anyother values. because NULL is not equal to a blank value, please try below query

    select case when @v1 <> isnull(@v2, '') then 'DIFF' else 'NO DIVV' end
    select case when isnull(@v1,'') <> isnull(@v2, '') then 'DIFF' else 'NO DIVV' end

    Thanks & Regards Prasad DVR


    • Edited by DVR Prasad Friday, November 23, 2012 3:28 PM
    •  
     
  • Friday, November 23, 2012 3:12 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Prasad's answer will return NO DIFF if one of the variables contains NULL and the other contains the empty string.  If that is what you want then great.  But if you want to return DIFF if one of the variables contains NULL and the other has any other non-null value (including blanks or the empty string), then use

    select case when @v1 <> @v2 
  or (@v1 is null and @v2 is not null)
  or (@v1 is not null and @v2 is null)
  then 'DIFF' else 'NO DIFF' end

    Tom

    • Proposed As Answer by scott_morris-ga Friday, November 23, 2012 3:18 PM
    • Marked As Answer by Miljan Radovic Sunday, November 25, 2012 6:44 PM
    •