Return to Answer

added 77 characters in body

Source Link

edited Aug 15, 2015 at 17:50

3.2k
2
21
35

Convert NULL values in empty string by wrapping it in COALESCE or IFNULL:

IFNULL:

IFNULL:

SELECT
    CONCAT(IFNULL(tadd.street_number,''),
        ' ',IFNULL(tadd.street_name,''),
        ', ',IFNULL(tadd.apt_number,''),
        ', ',IFNULL(tadd.city,''),
        ', ',IFNULL(tadd.postal_code,''),
        ', ',IFNULL(tadd.country,'')) AS 'Address'
FROM db.tbl;

COALESCE:

COALESCE:

SELECT
    CONCAT(COALESCE(tadd.street_number,''), 
        ' ',COALESCE(tadd.street_name,''),
        ', ',COALESCE(tadd.apt_number,''),
        ', ',COALESCE(tadd.city,''),
        ', ',COALESCE(tadd.postal_code,''),
        ', ',COALESCE(tadd.country,'')) AS 'Address'
FROM db.tbl

Convert NULL values in empty string by wrapping it in COALESCE or IFNULL:

IFNULL:

SELECT
    CONCAT(IFNULL(tadd.street_number,''), ' ',IFNULL(tadd.street_name,''),', ',IFNULL(tadd.apt_number,''),', ',IFNULL(tadd.city,''),', ',IFNULL(tadd.postal_code,''),', ',IFNULL(tadd.country,'')) AS 'Address'
FROM db.tbl;

COALESCE:

SELECT
    CONCAT(COALESCE(tadd.street_number,''), ' ',COALESCE(tadd.street_name,''),', ',COALESCE(tadd.apt_number,''),', ',COALESCE(tadd.city,''),', ',COALESCE(tadd.postal_code,''),', ',COALESCE(tadd.country,'')) AS 'Address'
FROM db.tbl

Convert NULL values in empty string by wrapping it in COALESCE or IFNULL:

IFNULL:

SELECT
    CONCAT(IFNULL(tadd.street_number,''),
        ' ',IFNULL(tadd.street_name,''),
        ', ',IFNULL(tadd.apt_number,''),
        ', ',IFNULL(tadd.city,''),
        ', ',IFNULL(tadd.postal_code,''),
        ', ',IFNULL(tadd.country,'')) AS 'Address'
FROM db.tbl;

COALESCE:

SELECT
    CONCAT(COALESCE(tadd.street_number,''), 
        ' ',COALESCE(tadd.street_name,''),
        ', ',COALESCE(tadd.apt_number,''),
        ', ',COALESCE(tadd.city,''),
        ', ',COALESCE(tadd.postal_code,''),
        ', ',COALESCE(tadd.country,'')) AS 'Address'
FROM db.tbl

Source Link

created Aug 13, 2015 at 17:23

oNare

3.2k
2
21
35

Convert NULL values in empty string by wrapping it in COALESCE or IFNULL:

IFNULL:

SELECT
    CONCAT(IFNULL(tadd.street_number,''), ' ',IFNULL(tadd.street_name,''),', ',IFNULL(tadd.apt_number,''),', ',IFNULL(tadd.city,''),', ',IFNULL(tadd.postal_code,''),', ',IFNULL(tadd.country,'')) AS 'Address'
FROM db.tbl;

COALESCE:

SELECT
    CONCAT(COALESCE(tadd.street_number,''), ' ',COALESCE(tadd.street_name,''),', ',COALESCE(tadd.apt_number,''),', ',COALESCE(tadd.city,''),', ',COALESCE(tadd.postal_code,''),', ',COALESCE(tadd.country,'')) AS 'Address'
FROM db.tbl