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.

Answer 1

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

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