Susbtract a character plus a possible "," after and before him in a string comma separated

Question

Those are the cases on table column post_attending, post_attending could be: 5 or 5,6,7 or 6,7,5 or 6,5,7 or '' or longer string comma separated and 5 on this or not. How should I do to delete "5" and "possible" , surrounding it using in PDO query?

I tried this:

$q= "UPDATE table SET post_attending = SUBSTR(REPLACE(CONCAT(',', post_attending, ','), ? , ','), 2, LENGTH(REPLACE(CONCAT(',', post_attending, ','), ? , ',')) - 2) WHERE id = x "
$sql->execute(array(5,5,x));

but it leaves me a , when there is a 5 or 5 is at the end.

I have read this also TRIM(BOTH ',' FROM REPLACE(wishes, '(,)? :var (,)?', '')) don't really figured out the right syntax.

See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Feb 27 '13 at 16:18
@Miguel I am sure that my query would also give you the same result and much better than Gordon answer — vikas, Feb 27 '13 at 17:14

score 0 · Accepted Answer · answered Feb 27 '13 at 16:23

Try this:

update table
    set post_attending = (case when val like '5,%' then SUBSTRING(val, 3, LENgth(val) - 2)
                               when val like '%,5' then SUBSTRING(val, 1, length(val) - 2)
                               when val = '5' then ''
                               else REPLACE(val, ',5,', ',')
                           end)

vikas · Answer 2 · 2013-02-27T17:34:09.240

0

without using any CASE statement, and I believe it would be faster

UPDATE table SET post_attending = SELECT REPLACE(REPLACE(REPLACE(REPLACE(val, ',5,', ','), ',5', ''), '5,', ''),'5', '')

edited Feb 27 '13 at 17:34

answered Feb 27 '13 at 16:45

vikas

2,612
4
24
34

Susbtract a character plus a possible "," after and before him in a string comma separated

2 Answers2