Geek asks and answers

Get the extra comma ',' when using concat

I wrote a saved proc in sqlyog. It is quite long and performs all the required functions except for the concat expression, so I will list only this specific request.

UPDATE recipes_new 
SET co_auths = CONCAT(co_auths,',',c1id) 
WHERE id = name_in;

Basically I want separation in two fields, and this operator is placed in the cursor, so it is iterative. co_authsat the moment it is null, so I get the result as 1,2,3, where as I want it is 1,2,3. Any guesses on what might be the most appropriate solution?

+5
source share
3 answers

Using IF:

UPDATE recipes_new 
SET co_auths = IF(co_auths IS NULL, c1id, CONCAT(co_auths, ',', c1id))
WHERE id = name_in;

If the value co_authsis an empty string instead NULL:

UPDATE recipes_new 
SET co_auths = IF(LENGTH(co_auths), CONCAT(co_auths, ',', c1id), c1id)
WHERE id = name_in;
+6
source

CASE, , NULL:

CONCAT(
    CASE 
        WHEN IFNULL(co_auths,'') = ''
        THEN '' 
        ELSE CONCAT(co_auths, ',') 
    END, c1id)
+2

MySQL returns an empty field: CONCAT (nonEmpty1, empty2, nonEmpty3) = NULL CONCAT_WS is what you are looking for

UPDATE recipes_new SET co_auths = CONCAT_WS(co_auths,',',c1id) WHERE id = name_in;
0
source

More articles:


All Articles