Replacing part of a string SQL based on condition

I have a use case in amazon redshift which I have a list of numbers in a column: E.g: 1,2,3,4,5,6 For this use case, the numbers have to be in the same column. Lets say that I want to pass a parameter and remove that number from the list. The thing I try to do is:

IF FIRST ENTRY THEN
update table set number = replace(number,'{0},','') where 
username = '{1}';
ELSE
update table set number = replace(number,',{0}','') where 
username = '{1}';
END IF;

So basically I try to remove the number from the list. The problem is removing the first number since there is no comma before that number. How can I remove the number+comma if it is the first entry and remove comma+number for all the other entries?

Thanks for any help in advance

Answer

You could append a ‘,’ at the start and end of the string and then replace occurances of “{0},”

After that replace any trailing or leading commas…

eg:

--First append a ',' to the start and end of the column "number". 
update table 
   set number = replace(concat(',',number,','),'{0},','') 
 where username = '{1}';

--Replace any leading or trailing commas...
update table 
   set number = btrim(number,',') 
 where username = '{1}';