Gowtham Ramamoorthy Gowtham Ramamoorthy - 6 months ago 11
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 ?

Begin
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
END

Answer

This ought to do you:

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

It works for solo CVs and more than 2.

Here is an example