SQL Server T-SQL Replace

Question

I have a case statement where I am trying to change two values from a field.

CASE
WHEN prod_map.Product_ID1 = 'CR'  
THEN REPLACE(REPLACE(cl.trade_day_count, 'ACT','ACTUAL'),cl.trade_day_count, 
'ACT+1', 'ACTUAL')
END,

so, when cl.trade_day_count contains the value 'ACT' or 'ACT+1' then change to 'ACTUAL'

The result of the 1st replace is the value you want to use as the 1st parameter on the 2nd replace function; thus you don't need cl.trade_date_count again. Replace takes 3 parameters if I recall, your outer most is showing 4. — xQbert
– xQbert, Commented Sep 21, 2017 at 17:29
If we can assume ~ isn't in cl.trade_day_count and never will be... rextester.com/XYVO11480 Replace(REPLACE(REPLACE('~'+cl.trade_day_count+'~', '~ACT+1~','ACTUAL'),'~ACT~', 'ACTUAL'),'~','') — xQbert
– xQbert, Commented Sep 21, 2017 at 17:59

Scath · Accepted Answer · 2017-09-22 17:23:09Z

2

Working answer from Alex K. comment:

REPLACE(REPLACE(cl.trade_day_count, 'ACT','ACTUAL'),'ACTUAL+1', 'ACTUAL')

edited Sep 22, 2017 at 17:23

answered Sep 21, 2017 at 17:16

Scath

3,81710 gold badges32 silver badges40 bronze badges

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

9 Comments

Alex K. Over a year ago

This would replace ACT+1 -> ACTUAL+1 but I think he wants ACTUAL on its own & has an overlap problem

xQbert Over a year ago

REPLACE(REPLACE(cl.trade_day_count, 'ACT','ACTUAL'),'ACT+1', 'ACTUAL') seems to work fine for ACT or ACT+1 values.

xQbert Over a year ago

@fercstar Regarding edit: How do you know they don't want all other values to be null? the else wasn't in the original question. maybe when it's CR they only want ACT and ACT+1 values but displayed with Actual?

Alex K. Over a year ago

the inner ACT to ACTUAL makes ACT+1 into ACTUAL+1 which does not match the next ACT+1 replace, REPLACE(REPLACE(cl.trade_day_count, 'ACT','ACTUAL'),'ACTUAL+1', 'ACTUAL') would work I think.

xQbert Over a year ago

@alexK you're right. I even wrote a demo and misread the results!

|

Collectives™ on Stack Overflow

SQL Server T-SQL Replace

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related