MySQL - Concat only when not empty

Question

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

coalesce(object,'') essential replace null with empty string. Coalese takes the first non-null value in a series. dev.mysql.com/doc/refman/8.0/en/… — xQbert
– xQbert, Commented Dec 14, 2021 at 22:32

nbk · Accepted Answer · 2021-12-14 22:41:29Z

2

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

answered Dec 14, 2021 at 22:41

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ahmad Sami Over a year ago

That worked like a charm. can I add another query to support empty strings too not just null?.

nbk Over a year ago

use image IS NOT NULL OR image <> ''

Ahmad Sami Over a year ago

Thank you so much. And I just checked your account and you look like you are very good in this. Can you recommend me a book about database?, I find it really interesting and really want to learn more about it. Thanks in advance.

nbk Over a year ago

look first at the links here stackoverflow.com/tags/mysql/info there are video tutorials and for beginners w3school and other lot s and lots, but look around this tag or the sql tag and read what is asked and the answers, they provide also good explanations and buzzwords to look for the manual also is a good starting point

xQbert · Accepted Answer · 2021-12-14 22:33:58Z

0

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

answered Dec 14, 2021 at 22:33

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySQL - Concat only when not empty

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related