replace the string in SQL

I have column like below

co1

a,b,c,d,e,f
a,b,d,e,f,c,c,c
b,a,e,c,b,z,a,a

I need as

b,d,e,f
b,d,e,f
b,d,e,f
b,e,b,z

I just need to remove a and c from the column and comma if any.
LVL 5
VIVEKANANDHAN_PERIASAMYAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
/* The following would not alter co1 if it is equal to 'a' or equal to 'c' */

update table1
set col1 = replace( replace( replace( replace(co1,'a,','') ,'c,','') ,',a','') ,',c','')
;

/* the following will alter co1 if it is equal to 'a' or equal to 'c' */

update table1
set col1 = replace( replace( replace( replace( replace( replace(co1,'a,','') ,'c,','') ,',a','') ,',c','') ,'a','') ,'c','')
;

Open in new window

0
 
Jesus RodriguezIT ManagerCommented:
UPDATE YOUR_TABLE
SET COL1=replace(replace(replace(col1,'a',''),'b',''),',,')

-- removing when star in ',' after the update
UPDATE YOUR_TABLE
SET COL1=SUBSTRING(2,LEN(COL1)-1)
WHERE LEFT(COL1,1)=',')

-- removing when ends in ',' after the update
UPDATE YOUR_TABLE
SET COL1=LEFT(COL1,LEN(COL1)-1)
WHERE RIGHT(COL1,1)=',')
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Can't we get it in one SQL statement?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.