Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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.
0
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
1 Solution
 
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
 
PortletPaulCommented:
/* 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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now