MySQL CONCAT multiple fields with IF statement

Question

I'm trying to do something I thought would be simple, but I'm stuck. I basically want to create a single address field from multiple address part fields, using an IF statement to use either an address or intersection. Here is my statement to make the field:

        CONCAT(loc_name,'\n',
            IF ( add_number != '' && add_street != '' ) THEN 
                CONCAT(add_number,' ',add_street,'\n')
            ELSEIF ( x_street_1 != '' && x_street_2 != '' ) THEN 
                CONCAT(x_street_1,' &amp; ',x_street_2,'\n')
            END IF
        ,city,', ', 
            IF ( state != '') THEN 
                CONCAT(state,' ',country,'\n')
            ELSEIF ( x_street_1 != '' && x_street_2 != '' ) THEN 
                CONCAT(country,'\n')
            END IF
        ) AS loc_info

But it doesn't like what I am doing at all, it throws an error at:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN \n\t\t\t\t\t\tadd_number,' ',add_street,'\n'\n\t\t\t\t\tELSEIF ( x_street_1 != '' && x_"

Which seems like it doesn't like my empty field ('') notation. But I don't know why. Can I not use the IF statement inside a CONCAT like that?

Thanks for any insight.

Jason Swett · Accepted Answer · 2013-01-25 17:14:20Z

13

IIRC, the syntax you want to use is

IF(condition, expression_if_true, expression_if_false)

I could be wrong, but you might want to try that.

answered Jan 25, 2013 at 17:14

Jason Swett

45.4k70 gold badges231 silver badges360 bronze badges

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

Comments

Kermit · Accepted Answer · 2013-01-25 17:16:12Z

6

The syntax is not correct. You want to use CASE:

SET @loc_name = 'Location';
SET @add_street = 'Add Street';
SET @add_number = '10';
SET @x_street_1 = 'Street 1';
SET @x_street_2 = 'Street 2';
SET @city = 'City';
SET @state = 'State';
SET @country = 'Country';

SELECT Concat(@loc_name, '\n', CASE 
                                 WHEN @add_number != '' 
                                      AND @add_street != '' THEN 
                                 Concat(@add_number, ' ', @add_street, '\n') 
                                 WHEN @x_street_1 != '' 
                                      AND @x_street_2 != '' THEN 
                                 Concat(@x_street_1, ' &amp; ', @x_street_2, 
                                 '\n') 
                               end, @city, ', ', CASE 
                                                   WHEN @state != '' THEN 
       Concat(@state, ' ', @country, '\n') 
              WHEN ( @x_street_1 != '' 
                     AND @x_street_2 != '' ) THEN Concat(@country, '\n') 
                                                 end) AS loc_info

Result

|                                    LOC_INFO |
-----------------------------------------------
| Location
10 Add Street
City, State Country
 |

Just find and replace @ with .

answered Jan 25, 2013 at 17:16

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

1 Comment

dmgig Over a year ago

Cool, and I found this quick explanation on the difference between CASE and IF: stackoverflow.com/questions/13699908/…

sourcecode · Accepted Answer · 2013-01-25 18:01:33Z

2

this might also help:

CONCAT(loc_name,'\n',
            IF ( add_number != '' && add_street != '' , 
                CONCAT(add_number,' ',add_street,'\n'),
                IF ( x_street_1 != '' && x_street_2 != '' , 
                   CONCAT(x_street_1,' &amp; ',x_street_2,'\n'),""
                   )
               ),

        city,
         ',' , 
            IF ( state != '',
                CONCAT(state,' ',country,'\n'),
                IF ( x_street_1 != '' && x_street_2 != '' , 
                   CONCAT(country,'\n'),""
                   )
               ) AS loc_info

also what are you comparing here state != '' is it against null values??
if so this will give you incorrect answer you have to use state IS NOT NULL instead of that.

answered Jan 25, 2013 at 18:01

sourcecode

1,8122 gold badges16 silver badges17 bronze badges

1 Comment

dmgig Over a year ago

That's good to know about the null. But I believe the are all empty, non-null fields.

Collectives™ on Stack Overflow

MySQL CONCAT multiple fields with IF statement

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related