We help IT Professionals succeed at work.

Mysql SELECT: possible to concantenate fields ONLY if they contain data?

freestate
freestate asked
on
I have a database driven eCommerce site, that captures address info in three fields:

Address1
Address2
Address3

However, my fulfilment provider only accepts two fields:

Address1
Address2

I have been able to CONCAT Address2 and Address3, placing ', ' in between them - however, if there is no Address2 or no Address3 this results in a pointless comma, which breaks the fulfilment system!

Is it possible to somehow check if Address2 and Address3 contain data, and only then CONCAT with a comma in between them?

Here's the query in use at the moment:

  
SELECT o.id AS orderID, o.shiptotal AS fPostageCost, CONCAT(o.dateyear,'-',o.datemonth,'-',o.dateday) AS dReceievedDate, o.ordertotal AS fTotalCharge, o.taxctotal AS fTax, 'GBP' AS cCurrency, '' AS PostalServiceTag, o.id AS ReferenceNum, CONCAT(oi.fname,' ',oi.lname) AS cFullName, o.customtwo AS cShippingAddress, oi.postalcode AS cPostCode, oi.country AS Country, o.email AS cEmailAddress, '' AS PackagingGroup, o.customone AS addCompany, CONCAT(oi.fname,' ',oi.lname) AS addName, oi.addone AS addAddress1, CONCAT(oi.addtwo,', ',oi.addthree) AS addAddress2, oi.city AS addTown, oi.stateprov AS addCounty, oi.country AS addCountry, oi.postalcode AS addPostCode, o.phone AS CustomerPhoneNumber
FROM ecom_orders o, ecom_orderitems oi WHERE status = 'NS' AND o.id = oi.orderid GROUP BY o.id

Open in new window

Comment
Watch Question

View Solutions Only

a_knows

Commented:
use IF

IF(address2<>'',<some value>,<else value>)
a_knows

Commented:
something like this:

IF(address2<>'',CONCAT(oi.addtwo,', ',oi.addthree),'')
pdd1lan

Commented:
can you use CASE stament to check?

SELECT ...
CASE addAddress2
  WHEN addAddress2  value will be nulll
  THEN doing something
ELSE
   CONCAT(oi.addtwo,', ',oi.addthree)
END AS addAddress2Check
freestate

Author

Commented:
OK, so if I need the check to be:

If address2 & address3 are not empty:

CONCAT(oi.addtwo,', ',oi.addthree) AS addAddress2,

If address2 is present bu address3 is empty:

oi.addtwo AS addAddress2,

If address3 is present but address2 is empty:

oi.addthree AS addAddress2,

How would I incorporate that in to the above SELECT statement?
a_knows

Commented:
if(addtwo<>'',IF(addthree<>'',CONCAT(),addrtwo),addthree)
a_knows

Commented:
there was a typo:
if(addtwo<>'',IF(addthree<>'',CONCAT(addtwo,', ',addthree),addtwo),addthree)
freestate

Author

Commented:
so do I just put:

if(addtwo<>'',IF(addthree<>'',CONCAT(addtwo,', ',addthree),addtwo),addthree) AS addAddress2

or does the AS need to be worked in to the If statement somewhere?
Commented:
if(addtwo<>'',IF(addthree<>'',CONCAT(addtwo,', ',addthree),addtwo),addthree) AS addAddress2

 try and tell us if it soved your problem.

cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Try this, replace

CONCAT(oi.addtwo,', ',oi.addthree) AS addAddress2

with

CASE
WHEN oi.addtwo IS NOT NULL AND oi.addthree IS NOT NULL THEN CONCAT(oi.addtwo,', ',oi.addthree)
WHEN oi.addtwo IS NOT NULL THEN oi.addtwo
WHEN oi.addthree IS NOT NULL THEN oi.addthree
ELSE '' END AS addAddress2
cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
This is better

CONCAT_WS(', ',nullif(oi.addtwo,''),nullif(oi.addthree,'')) AS addAddress2
a_knows

Commented:
Wrong, that adds a ', ' no matter what addtwo and addthree are. It should return '' instead.
freestate

Author

Commented:
The IF statement is slightly shorter than using Case, but both seemed to work OK