Gowtham Ramamoorthy Gowtham Ramamoorthy - 4 months ago 7x
SQL Question

How to replace multiple string in a single select statement?

I'm trying to replace strings in a table but couldnt get what I need. Below is the sample code for replacing strigns which I tried.

Problem: I need to replace the string 'CV7 + CV8' with exact matching values.

For eg: if I have values like CV7 1998,CV8 1998 which needs to replaced as

CV7 1998 + CV8 1998
without any duplicates. so my desired ouptut should be

CV7 1998 + CV8 1998
CV7 1999 + CV8 1999

How can I achieve this in a single selece statement ?

Declare @variable as varchar(50)
set @variable = 'CV7 + CV8'

CREATE TABLE #CVtableforallyears
Outcomedestination Varchar(160),
TimeDimensionDatefromCV date
Insert into #CVtableforallyears values
('CV7 1998','1998-01-01'),
('CV7 1999','1999-01-01'),
('CV7 2000','2000-01-01'),
('CV7 2001','2001-01-01'),
('CV7 2002','2002-01-01'),
('CV8 1998','1998-01-01'),
('CV8 1999','1999-01-01'),
('CV8 2000','2000-01-01'),
('CV8 2001','2001-01-01'),
('CV8 2002','2002-01-01')

Select Replace(REPLACE(@variable,'CV7',LTRIM (RTRIM (Outcomedestination))),'CV8',Outcomedestination) from #CVtableforallyears
Drop table #CVtableforallyears


This ought to do you:

select distinct 
  STUFF((SELECT distinct ' + ' + t2.Outcomedestination
         from #CVtableforallyears t2
         where t1.TimeDimensionDatefromCV = t2.TimeDimensionDatefromCV
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
   ,3,1000) as list
from #CVtableforallyears t1

It works for solo CVs and more than 2.

Here is an example