Greater-Than Empty String Comparison Yielding Unexpected Results

  • dave-L

    SSCommitted

    Points: 1512

    I'm not sure when, but years ago as a BASIC programmer, I picked up the habit of checking to see if a string was not an empty string by asking if it was "greater-than" an empty string. Something like this:

    IF(myString > "")

    When I began writing T-SQL this naturally transitioned into:

    WHERE myColumn > ''

    What I'm actually asking here is "Is myColumn not equal to an empty string?", and yes -- I acknowledge that I should (and now I will) write my comparisons this way:

    WHERE myColumn <> ''

    What I discovered today is my original "greater-than empty string" comparison works just find in T-SQL unless the column you are comparing to starts with a control character.  Below is an example:

    DECLARE @myColumn VARCHAR(10) = 'Dave'

    SELECT 'This works!' WHERE @myColumn > ''

    SET @myColumn = CHAR(9) + @myColumn

    SELECT 'But this does not.' WHERE @myColumn > ''

    SELECT 'This does.' WHERE @myColumn <> ''

    This result was unexpected for me and doesn't make a lot of sense. I'm now looking at old code for places where I used a "greater-than empty string" comparison.

    *UPDATE*

    Incidentally, I tested the above comparisons with NVARCHAR and all tests passed, even the "greater-than empty string" test.

     

    • This topic was modified 4 days, 18 hours ago by  dave-L.
    • This topic was modified 3 days, 23 hours ago by  dave-L. Reason: Additional testing with NVARCHAR
  • SGT_squeequal

    SSCertifiable

    Points: 7064

    Char(9) is a horizontal tab and has a Ascii value of 9, select '' yields null

    select ascii(char(9)),ascii('')

    what are you explicitly trying to do? are you looking where your column is not an empty string?

     

     

     

     

    ***The first step is always the hardest *******

  • Jonathan AC Roberts

    SSCoach

    Points: 16879

    Two single quotes together ('') is evaluated to be the same in an inequality expression as a space character [char(32)].

    Couldn't you just use char(9) for your comparison instead of the quotes?:

    DECLARE @myColumn VARCHAR(10) = 'Dave'

    SELECT 'This works!' WHERE @myColumn > char(9)

    SET @myColumn = CHAR(9) + @myColumn

    SELECT 'But this does not.' WHERE @myColumn > char(9)

    SELECT 'This does.' WHERE @myColumn <> char(9)

     

     

     

  • ScottPletcher

    SSC Guru

    Points: 98115

    I'd say just use > CHAR(0) if you're going to go that route.  Btw, wouldn't you have to use >= CHAR(9), just in case only a tab char was there?

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • dave-L

    SSCommitted

    Points: 1512

    @SGT_squeequal, @Jonathan AC Roberts, @scottpletcher:

    I'm sorry I wasn't clear. I've solved my problem. The solution is to use <> ("not equal to") instead of > ("greater-than"). It just surprised to me that in all instances > works except if the comparison string starts with a control character.

    I just wanted to put this out for anyone else who might be using "greater-than empty string" instead of "not equal to empty string".

    Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply