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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply