Greater-Than Empty String Comparison Yielding Unexpected Results

  • 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 5 years, 1 month ago by  David Lafayette.
    • This topic was modified 5 years, 1 month ago by  David Lafayette. Reason: Additional testing with NVARCHAR
  • 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 *******

  • 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)

     

     

     

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • @SGT_squeequal, @jonathan-2 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 4 (of 4 total)

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