CODEWITHSUNDEEP
mysqlstringcoldfusion-2016
Kay Singian
Kay Singian

Reputation: 1381

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

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.

Upvotes: 4

Views: 6152

Answers (2)

geithman
geithman

Reputation: 1

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)

Upvotes: 0

Biac
Biac

Reputation: 46

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

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

Upvotes: 3

Related Questions