MySQL concatenation when there are null fields

I created a VIEW using this code:

CREATE OR REPLACE VIEW aaa AS

   SELECT pry.uid, 
          treg.nombre_es as region, 
          tpais.nombre_es as pais, 
          tdep.departamento, 
          dep_other, tciu.ciudad, 
          ciu_other
     FROM tx_oriproyectos_proyectos AS pry
LEFT JOIN tx_oritablascomunes_regiones as treg ON pry.region = treg.uid
LEFT JOIN tx_oritablascomunes_paises as tpais ON pry.pais = tpais.uid
LEFT JOIN tx_oritablascomunes_departamentos as tdep ON pry.departamento = tdep.uid
LEFT JOIN tx_oritablascomunes_ciudades as tciu ON pry.ciudad = tciu.uid

And I obtained this and is Ok:

result http://finewebdesigns.com/images/mysql_view_result.jpg

And now I need to obtain concatenate results like this:

concatenated_field
---------------------------------------
Africa - ALbania - Tirana1 - Tirana2
Africa - Colombia - Guaviare - Calamar

How can I do that?

I tried this:

CREATE OR REPLACE VIEW aaa AS

SELECT CONCAT_WS (' - ', pry.uid, treg.nombre_es as region, tpais.nombre_es as pais, tdep.departamento, dep_other, tciu.ciudad, ciu_other)
FROM tx_oriproyectos_proyectos AS pry
LEFT JOIN tx_oritablascomunes_regiones as treg
ON pry.region=treg.uid
LEFT JOIN tx_oritablascomunes_paises as tpais
ON pry.pais=tpais.uid
LEFT JOIN tx_oritablascomunes_departamentos as tdep
ON pry.departamento=tdep.uid
LEFT JOIN tx_oritablascomunes_ciudades as tciu
ON pry.ciudad=tciu.uid

But I obtained:

#1583 - Incorrect parameters in the call to native function 'CONCAT_WS'

Ok, Thanks to @Mat I finally get this code, that is t开发者_如何学运维he expected solution to this problem.

CREATE OR REPLACE VIEW aaa AS

SELECT pry.uid, CONCAT_WS (' - ', treg.nombre_es, tpais.nombre_es, tdep.departamento, NULLIF(dep_other,''), tciu.ciudad, NULLIF(ciu_other,''))
FROM tx_oriproyectos_proyectos AS pry
LEFT JOIN tx_oritablascomunes_regiones as treg
ON pry.region=treg.uid
LEFT JOIN tx_oritablascomunes_paises as tpais
ON pry.pais=tpais.uid
LEFT JOIN tx_oritablascomunes_departamentos as tdep
ON pry.departamento=tdep.uid
LEFT JOIN tx_oritablascomunes_ciudades as tciu
ON pry.ciudad=tciu.uid

That obtains this: http://finewebdesigns.com/images/mysql_view_result_solved.jpg

You should be able to use the CONCAT_WS string function.

SELECT CONCAT_WS(' - ', treg.nombre_es, tpais.nombre_es, ...) FROM ...

From the docs:

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

so your null values will simply be ignored.

If you also want to skip empty strings, you can use throw the NULLIF function in the mix, as suggested by ypercube:

SELECT CONCAT_WS(' - ', NULLIF(col,''), ...) ...