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.
October 10, 2019 at 8:49 am
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 *******
October 10, 2019 at 2:44 pm
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)
October 10, 2019 at 3:10 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy