MySQL syntax for conditional CONCAT depending on a field content?

Question

I have inherited a simple table from a client, that stores two addresses (shipping address and business address) in just two fields shipping_address and business_address. There is a field called ship_to with enum('shipping','business'), and a field for a full name. The client wants an output as:

full name: address

Where "address" column should be picked by the ship_to value. If ship_to = shipping then use shipping_address column. If ship_to = business then use business_address column.

For instance:

Joe Smith: Roach Street #10, Rats Village

How I can use CONCAT with IF condition? I'm guessing something like this, which obviusly is just an idea:

SELECT CONCAT( full_name, ':', IF (ship_to=='shipping', shipping_address, business_address ) AS contact FROM `table`

But, what's the right syntax for doing this?

you are missing a closing ) and also in SQL compare is done with a single = not == — Litmus
– Litmus, Commented Nov 15, 2013 at 3:53
I just tried but only works if I prefix the fields, that's it, if I specify table.full_name, table.ship_to, table.shipping_address and table.business_name...I wonder if that's really needed or it is a kind of server issue? It doesn't work at all without the table. — Aram Alvarez
– Aram Alvarez, Commented Nov 15, 2013 at 3:56
@AramAlvarez What sort of error produced, if table name prefix not specified? — Jason Heo
– Jason Heo, Commented Nov 15, 2013 at 4:02
very confusing situation. Did you tried CASE rather than IF? also Did you run query in mysql console? — Jason Heo
– Jason Heo, Commented Nov 15, 2013 at 4:17

Edper · Accepted Answer · 2013-11-15 03:59:06Z

2

Lacking the closing )

SELECT 
CONCAT( full_name, ':', IF (ship_to='shipping', shipping_address, business_address )) as    contact
FROM TableName

edited Nov 15, 2013 at 3:59

answered Nov 15, 2013 at 3:52

Edper

9,3621 gold badge32 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

MySQL syntax for conditional CONCAT depending on a field content?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related