mysql concate check not null

Question

SELECT Concat(mp.cHospital ,',',mp.cHospital1,',',mp.cHospital2) as Hospital FROM TBL

I dont want to return null value,

how to check not null or how to make isset cond in query

Answer 1

By definition (almost)any operation with NULL will result in NULL as NULL means "undfined". I interpret your question that either cHospital or cHospital1 or cHospital3 might be NULL which you want to check. The question is: what should happen? You want just the field replaced with an empty stirng and then the concat or all?

I assume the first. that might look like this:

SELECT Concat(
    IFNULL(mp.cHospital, ''),
    ',',
    IFNULL(mp.cHospital1,''),
    ',',
    IFNULL(mp.cHospital2,'')) AS Hospital
FROM TBL

IFNULL returns the first part, unless its NULL where it returns the second part(empty string here).

http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_ifnull

Answer 2

I recommend using CONCAT_WS function. This works according to what you need. Would be something like

SELECT CONCAT_WS(',', mp.cHospital, mp.cHospital1, mp.cHospital2) as Hospital FROM TBL

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

Greetings!!