Comparing empty strings if it is not equal to MySQL column with string values

Question

I'm not sure if this scenario is related only to Coldfusion or Mysql.

If a column that is of type TEXT is compared to an empty string value if it is not equal, this code works:

column > ""

but not this one:

column != ""

In my SQL code, let's say the database column attachment with a TEXT type has strings contained in it. The Form value of the Input Attachment (:attachment) is empty (I think it will refer to "").

attachment = ( 
    CASE 
        WHEN :attachment = "" AND attachment = "" 
            THEN 1 
        WHEN :attachment = "" AND attachment != "" 
            THEN 2 
        WHEN :attachment != "" AND attachment = "" 
            THEN 3
        WHEN :attachment != "" AND attachment != "" 
            THEN 4 
        ELSE attachment 
    END 
)

this code would return attachment = 3.

However, if I use this SQL code:

attachment = ( 
    CASE 
        WHEN :attachment = "" AND attachment = "" 
            THEN 1 
        WHEN :attachment = "" AND attachment > "" 
            THEN 2 
        WHEN :attachment > "" AND attachment = "" 
            THEN 3
        WHEN :attachment > "" AND attachment > "" 
            THEN 4 
        ELSE attachment 
    END 
)

This code returns attachment = 2 which is what I really am expecting to have.

So, what's the best way to compare a column if it is equal or if it is not equal to an empty string? I rarely use NULL values because when FORM are saved, they input empty strings.

Possible duplicate of Checking for an empty field with MySQL — Pyromonk
– Pyromonk, Commented Jun 3, 2017 at 0:53
Don't use double quotes to compare strings in the database change all of then to single quotes and test it again! — Jorge Campos
– Jorge Campos, Commented Jun 3, 2017 at 0:53
@Pyromonk I'm checking if it is not equal, not if it is equal. I check the link you tagged and I didn't seem to find the exact answer to my question. — Kay Singian
– Kay Singian, Commented Jun 3, 2017 at 0:57
SQL ANSI standards. Double quotes is for identifiers as if you are creating a new column name. Single quotes is for string comparison. — Jorge Campos
– Jorge Campos, Commented Jun 3, 2017 at 0:58

Biac · Accepted Answer · 2017-06-03 00:57:42Z

3

I believe you should use <> instead of != and also use single quotation instead of double quotation e.g.

WHEN :attachment <> '' AND attachment <> ''

answered Jun 3, 2017 at 0:57

Biac

462 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael - sqlbot Over a year ago

Neither of these things should actually be relevant in the MySQL dialect. Double quotes for strings are accepted unless the server has ANSI_QUOTES enabled, and if it did, these queries would throw an error. For inequality, != and <> are equivalent.

geithman · Accepted Answer · 2020-09-15 14:16:57Z

0

Warning: NEVER use column > ''

I just had some cases with MySql 5.7, where column > '' resulted in FALSE, but column < '' resulted in TRUE!!

So better check for LENGTH(column)

answered Sep 15, 2020 at 14:16

geithman

11 bronze badge

Collectives™ on Stack Overflow

Comparing empty strings if it is not equal to MySQL column with string values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related