CODEWITHSUNDEEP
sqlsql-server
Sean
Sean

Reputation: 163

SQL Replace Multiple Values based on one column value

I need to replace results on up to 9 columns based on one other column value. If the column "role" is the value "SeniorManagement", I want to replace what would be default hierarchical values with hard values. Here's how I can accomplish this now:

 , CASE
   WHEN d.Role = 'SeniorManagement'  
   THEN (Replace(p.Firstname,'John','Joe')) END as First
 , CASE
   WHEN d.Role = 'SeniorManagement'  
   THEN (Replace(p.Lastname,'TopDog','AssignedPerson')) END as Last
 , CASE...

My question is, is there a less verbose way to combine these?

Pseudo ( I know this doens't work :-)

 , CASE
    WHEN d.Role = 'SeniorManagement' 
    THEN (Replace(p.Firstname,'John','Joe')) as First 
    THEN (Replace(p.Lastname,'TopDog','AssignedPerson')) as Last
    THEN (Replace(p.Email,'TopDog@wherever','AssignedPerson@wherever')) as Email

Thanks

Upvotes: 1

Views: 3260

Answers (2)

Gonzalo.-
Gonzalo.-

Reputation: 12672

what about using a function?

create function dbo.GetColumnValue
(
   @ColumnBasedValue varchar(max),
   @ColumnToReplace varchar(max), --or the type you want
   @ToReplace varchar(max), 
   @Replacement varchar(max)
)
as
BEGIN

return (select case @ColumnBasedValue when 'SeniorManagement'
                      then (Replace(@ColumnToReplace,@ToReplace,@Replacement))
                      else @replaceValue END)

END

and use it like 

select dbo.GetColumnValue(d.Role, p.Firstname,'John','Joe') as First, 
dbo.GetColumnValue(d.Role, p.Lastname,'TopDog','AssignedPerson') as Last..

Upvotes: 1

gbronner
gbronner

Reputation: 1945

Depending on what the context is, you might be able to get away with doing raw selects.

Select d.Role, 'Joe' as First, 'AssignedPerson' as Last

and just filter each select with a where clause.

If you want to do multiple ones, use a union.

Upvotes: 0

Related Questions