CODEWITHSUNDEEP
phpmysql
charlie
charlie

Reputation: 481

SQL CONCAT if column is not null

I am using this SELECT query:

SELECT sequence, company,
    CONCAT(address1, '\r', address2, '\r', address3, '\r', town, '\r', county, '\r', postcode) AS address
FROM customer
WHERE company_status = 'Customer'
ORDER BY company ASC

but sometimes, some of the address columns may be null/blank so i do not want to include them

i am using this query in PHP, so i have tried:

str_replace(array("\r","\n"), '', $result["address"])

but that is just removing ALL line break between the addresses, how can i remove only the blank lines?

Upvotes: 0

Views: 1256

Answers (3)

Sougata Bose
Sougata Bose

Reputation: 31749

Simply add the condition - 

CONCAT(IF(address1 IS NOT NULL, CONCAT(address1, '\r'), ''), IF(address2 IS NOT NULL, CONCAT(address2, '\r'), ''), ....

Upvotes: 3

Ohgodwhy
Ohgodwhy

Reputation: 50787

If you modify the select state and use the control flow function IFNULL, you can test if the column is null, and if so, use '' as the return value for this. Furthermore, you need to concat again within the first argument to the IFNULL statement to have the \r only appear when address1 is not null.

SELECT sequence, 
       company, 
       CONCAT(IFNULL(CONCAT(address1, '\r'),''), IFNULL(CONCAT(address2, '\r'),''), IFNULL(CONCAT(address3, '\r'),''), town, '\r', county, '\r', postcode) as address 
   FROM customer 
   WHERE company_status = 'Customer' 
   ORDER BY company A

Lastly, you'll notice that there is no support for empty text within the database. That's because it's considered a bad design to have both empty and null fields. Let's just fix that.

UPDATE customer SET address1=NULL WHERE MyColumn='';

Just run this for the address columns. Then set the default on the column to null.

Upvotes: 0

Serhat Akay
Serhat Akay

Reputation: 534

Something like this? 

$addressArr = array();
if (isset($result["address"])){
foreach ($result["address"] as $address) {
     if ($address) {
     $addressArr = $address;
     }
}
}
var_dump($addressArr);

Upvotes: 0

Related Questions