MySQL - Concat only when not empty-CodePudding

I'm trying to add link to my image when I return it from database but I don't want to add it when image is null. I tried CONCAT and CONCAT_WS but it didn't work

both of these didn't work:

SELECT id, name_en as name, CONCAT_WS("http://website.com/", image) as image FROM businesses

SELECT id, name_en as name, CONCAT("http://website.com/", image) as image FROM businesses

CodePudding user response：

you can use ÌF flowcoltrol,`to determine iif image is NULL and if not add the url

SELECT id, name_en as name, IF(image IS NOT NULL, CONCAT("http://website.com/", image),"") as image 
FROM businesses

CodePudding user response：

What you're looking for is the COALESCE() function: Doc link below. It returns the first non-null value in a series. so in essence we replace Null with an empty string.

SELECT id, name_en as name, CONCAT("http://website.com/", coalesce(image,'')) as image 
FROM businesses

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce