Greater-Than Empty String Comparison Yielding Unexpected Results

Question

Post reply

Greater-Than Empty String Comparison Yielding Unexpected Results

David Lafayette

SSCommitted

Points: 1559
More actions
October 10, 2019 at 12:01 am
Go to Answer
#3687604
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, 2 months ago by David Lafayette.
- This topic was modified 5 years, 2 months ago by David Lafayette. Reason: Additional testing with NVARCHAR
David Lafayette

SSCommitted

Points: 1559
More actions
October 10, 2019 at 4:58 pm
Answer
#3687787

@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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

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: 18363 More actions · Answer 2

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: 101123 More actions · Answer 3

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".