How to replace multiple string in a single select statement?

Question

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

Post (add to your question by using edit) your desired result. — PM 77-1
– PM 77-1, Commented Jun 3, 2016 at 22:05
Hi... posted my required output... let me know if this is fine... — Gowtham Ramamoorthy
– Gowtham Ramamoorthy, Commented Jun 3, 2016 at 22:17
its just a small part of a large stored procedure where I have this requirement to do... — Gowtham Ramamoorthy
– Gowtham Ramamoorthy, Commented Jun 3, 2016 at 22:24
Is the criteria to match based on the TimeDimension being the same, or the year in the string Outcomedest being the same? — EoinS
– EoinS, Commented Jun 3, 2016 at 22:24
Please post the exact desired result based on your sample data. — PM 77-1
– PM 77-1, Commented Jun 3, 2016 at 22:24

EoinS · Accepted Answer · 2016-06-03 23:11:08Z

1

This ought to do you:

SELECT DISTINCT
  substring(
   (SELECT 
      DISTINCT ' + ' + t2.Outcomedestination
    FROM #CVtableforallyears t2 
    WHERE t2.TimeDimensionDatefromCV = t1.TimeDimensionDatefromCV
    FOR XML Path ('')  
    )
  ,4,1000) --just to omit the first ' + '
FROM #CVtableforallyears t1; 
END

It works for solo CVs and more than 2.

Edited to pare down the unnecessary .The example below demonstrates using FOR XML PATH and then how to use that in your full query.

Basically we concatenate the entire query into a string but use '' to split where naturally <row> would occur; we concatenate each result with ' + '. Then you make your subquery only select matches to the parent query. Using DISTINCT to avoid duplication. Unfortunately there is no LISTAGG in TSQL so we use this method.

Here is an example

edited Jun 3, 2016 at 23:11

answered Jun 3, 2016 at 22:40

EoinS

5,4921 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mincong Huang · Accepted Answer · 2016-06-03 22:40:30Z

You don't need to use the REPLACE statement to do it.

WITH cv7 AS
(
    SELECT 
        Outcomedestination,
        TimeDimensionDatefromCV,
        RIGHT(LTRIM(RTRIM(Outcomedestination)), 4) AS year
    FROM #CVtableforallyears
    WHERE LEFT(LTRIM(RTRIM(Outcomedestination)), 3) = N'CV8'
),
cv8 AS
(
    SELECT 
        Outcomedestination,
        TimeDimensionDatefromCV,
        RIGHT(LTRIM(RTRIM(Outcomedestination)), 4) AS year
    FROM #CVtableforallyears
    WHERE LEFT(LTRIM(RTRIM(Outcomedestination)), 3) = N'CV8'
)
SELECT cv7.Outcomedestination + N' + ' + cv8.Outcomedestination
FROM cv7
INNER JOIN cv8
ON cv7.year = cv8.year

Here's the results :

CV8 1998 + CV8 1998
CV8 1999 + CV8 1999
CV8 2000 + CV8 2000
CV8 2001 + CV8 2001
CV8 2002 + CV8 2002

Collectives™ on Stack Overflow

How to replace multiple string in a single select statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related