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.
Incidentally, I tested the above comparisons with NVARCHAR and all tests passed, even the "greater-than empty string" test.
- This topic was modified 4 months, 2 weeks ago by dave-L.
- This topic was modified 4 months, 2 weeks ago by dave-L. Reason: Additional testing with NVARCHAR