user3725253 user3725253 -4 years ago 87
MySQL Question

Add other field to String in IF-Statement

I have the following query with an IF-Statement. If one field is empty, it should output a String with the value from another field.

SELECT IF(field1 IS NULL or field1 = '', 'Field is empty but field 2 says field2', field1 )AS field1 FROM...


When I enter the query like above it will only output the string


Field is empty but field 2 says field2


The output should be


Field is empty but field 2 says 5 (5 is the value of the field)


I also tried putting the field to into quotes, but that throws out a mysql-error.

I think maybe the way with the quotes is right, but I'm missing a string connector or something like that.

Any ideas?

Answer Source

Learn to use CASE. IF() is MySQL-specific. CASE is standard and available in most databases. But the answer to your question is CONCAT() or REPLACE():

SELECT (CASE WHEN field1 IS NULL OR field1  = ''
             THEN CONCAT('Field is empty but field 2 says ', field2)
             ELSE field1
        END) AS field1
FROM ... ;

I like to write such formats using REPLACE() as well:

SELECT (CASE WHEN field1 IS NULL OR field1  = ''
             THEN REPLACE('Field is empty but field 2 says {0}', '{0}', field2)
             ELSE field1
        END) AS field1
FROM ... ;

This makes it easier to see what the final string will look like.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download