Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • 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
 
PaulCommented:
/* 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
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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