Conditional REPLACE in SQL Server

Question

The table project contains

projectid|description|statuscode|path|....

I have a SQL clause which should return the following (pseudocode):

SELECT ...,  
     CASE   
     WHEN statusCode IN ('C', 'N', 'F') 
       IF (path contains 'CLOSED PROJECTS')
       THEN   
        REPLACE(path,'CLOSED PROJECTS','DEAD PROJECTS') 
       ELSE
        REPLACE(path,'ACTIVE PROJECTS','DEAD PROJECTS') 
,
.....
from Project where projectId=.....

How do I implement this 'conditional' REPLACE?

I am using SQL Server 2008.

Pரதீப் · Accepted Answer · 2014-10-24 07:08:00Z

3

May be like this..

SELECT ...,  
 CASE   
 WHEN statusCode IN ('C', 'N', 'F') 
   and path like '%CLOSED PROJECTS%'
   THEN   
    REPLACE(path,'CLOSED PROJECTS','DEAD PROJECTS') 
   ELSE
    REPLACE(path,'ACTIVE PROJECTS','DEAD PROJECTS') 
,
.....
from Project where projectId=.....

edited Oct 24, 2014 at 7:08

answered Oct 24, 2014 at 6:53

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

5 Comments

NickyvV Over a year ago

I think the contains predicate can only be used in the WHERE clause, so only the latter solution works.

Pரதீப் Over a year ago

@NickyvV - Yes you are right. Removed it from the answer

Kahn Over a year ago

Just to make sure, this can be understood in two ways. One: You want to replace CLOSED projects on rows where statusCode is C/N/F, and replace ACTIVE projects on all other rows regardless of statusCode (which is what the answer above does). Two: You you only want to do ANY kind of replacing on rows where statusCode is C/N/F, but what you replace path with, depends on whether it contains CLOSED or not. There is a difference. :)

Pரதீப் Over a year ago

@Kahn I agree with you and even i had the same question but by looking at the question and pseudo-code he/she just want to add the condition before replacing. I can't sit and assume things!!

Kahn Over a year ago

@Pradeep no that wasn't criticism, your answer is solid. I just noticed this question and wanted to make sure we're interpreting this the way OP wanted it to. :)

Collectives™ on Stack Overflow

Conditional REPLACE in SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related