Solved

replace the string in SQL

Posted on 2013-11-08
3
293 Views
Last Modified: 2013-11-10
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
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39635038
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
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39635088
Can't we get it in one SQL statement?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39635240
/* 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question