Get an extra comma ',' while using concat

Question

I have wrote a stored proc in sqlyog. It is quite long and performing all the desired functionality except for the concat statement so I will only list that particular query.

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

I basically want a separation in the two fields and this statement is placed in a cursor so it is iterative. co_auths is null at the moment so I get the result as ,1,2,3 where as I want it to be 1,2,3. Any guesses what can the most appropriate solution be?

Ja͢ck · Accepted Answer · 2013-02-22T14:59:51.567

7

By using an IF:

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

If the value of co_auths is an empty string instead of NULL:

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

edited Feb 22 '13 at 14:59

answered Feb 22 '13 at 14:40

Ja͢ck

170,779
38
263
309

UPDATE recipes_new SET co_auths= (IF(co_auths IS NULL, c1id, CONCAT(co_auths, ',', c1id))WHERE id=name_in); Am I right because im getting an error – Shahzaib Feb 22 '13 at 14:58
@Shahzaib You said the value of `co_auths` was null ... is it an empty string instead? – Ja͢ck Feb 22 '13 at 15:03
Oh sorry I confused empty and Null. Yes its empty – Shahzaib Feb 22 '13 at 15:04
@Shahzaib the reason I'm asking is because you unaccepted this answer. – Ja͢ck Feb 23 '13 at 09:07
Actually yours and @sgeddes both worked. So I ticked both, now I get it only one can be ticked at a time so since I found u to be much more helpful and knowledgeable I have again ticked accepted ur answer. Thanks again – Shahzaib Feb 23 '13 at 09:09
I posted another question today, can you please see that and tell me if you could help me in solving that – Shahzaib Feb 23 '13 at 09:11
@Shahzaib yes you can only accept one, but if other answers were useful to you, you should up vote them, since you have 20 rep now. – Ja͢ck Feb 23 '13 at 09:13
please see my other question http://stackoverflow.com/questions/15036056/repetition-of-last-number-on-update-in-a-stored-procedure – Shahzaib Feb 23 '13 at 09:16
@Shahzaib I've up voted that question, but I don't know the exact answer because I don't do PLSQL much – Ja͢ck Feb 23 '13 at 09:19
can you please tell me how to get the total number of records that are being returned on execution of a cursor? – Shahzaib Feb 23 '13 at 09:53
@Shahzaib Wish I could tell you, but I don't do stored procedures ;-) – Ja͢ck Feb 23 '13 at 09:54

sgeddes · Answer 2 · 2013-02-22T15:07:27.633

1

This should work using CASE to check if it's NULL:

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

edited Feb 22 '13 at 15:07

answered Feb 22 '13 at 14:39

sgeddes

62,311
6
61
83

The problem still remains there – Shahzaib Feb 22 '13 at 14:48
@Shahzaib -- Updated answer to check for blank and null -- best of luck! – sgeddes Feb 22 '13 at 15:07

score 1 · Answer 3 · edited May 23 '17 at 10:32

1

MySQL returning 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;

edited May 23 '17 at 10:32

Community

1
1

answered Feb 22 '13 at 14:41

JaMaBing

1,051
14
32

I have tried this already it displays only the fist number i.e out of 1,2,3 only 1 – Shahzaib Feb 22 '13 at 14:51
Use NULLIF to also skip empty fields as well as nulls https://stackoverflow.com/a/3924726/74585 – Matthew Lock Oct 24 '18 at 06:38

Get an extra comma ',' while using concat

3 Answers3