3

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'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Iasha
  • 77
  • 1
  • 8
  • 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 Sep 21 '17 at 17:29
  • If we can assume `~` isn't in `cl.trade_day_count` and never will be... http://rextester.com/XYVO11480 `Replace(REPLACE(REPLACE('~'+cl.trade_day_count+'~', '~ACT+1~','ACTUAL'),'~ACT~', 'ACTUAL'),'~','')` – xQbert Sep 21 '17 at 17:59

1 Answers1

2

Working answer from Alex K. comment: 

REPLACE(REPLACE(cl.trade_day_count, 'ACT','ACTUAL'),'ACTUAL+1', 'ACTUAL')
Scath
  • 3,777
  • 10
  • 29
  • 40
  • 1
    This would replace ACT+1 -> ACTUAL+1 but I think he wants ACTUAL on its own & has an overlap problem – Alex K. Sep 21 '17 at 17:19
  • 1
    `REPLACE(REPLACE(cl.trade_day_count, 'ACT','ACTUAL'),'ACT+1', 'ACTUAL')` seems to work fine for ACT or ACT+1 values. – xQbert Sep 21 '17 at 17:35
  • @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? – xQbert Sep 21 '17 at 17:36
  • 1
    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. – Alex K. Sep 21 '17 at 17:37
  • @alexK you're right. I even wrote a demo and misread the results! – xQbert Sep 21 '17 at 17:39
  • I don't have a DB to test it I thought it would only replace exact matches without the wildcard like : 'ACT%', 'ACTUAL' – Scath Sep 21 '17 at 17:40
  • You'd have to use an arbitrary value not in the cl.trade_day_count column and set each value to it then covert those to ACTUAL `Replace(REPLACE(REPLACE(cl.trade_day_count, 'ACT+1','~Fixme~'),'ACT', '~Fixme~'),'~Fixme~','ACTUAL')` http://rextester.com/WUDGB33936 – xQbert Sep 21 '17 at 17:43
  • @fercstar's edited it again I think that will cover both correct xQbert? – Scath Sep 21 '17 at 18:08
  • @scath Yes/No if the trade_Day_count can contain 'ACTUAL' as a value (or anything with just ACT) then you'd get ACTUALUAL) – xQbert Sep 22 '17 at 17:04