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

Answers


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..

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.


Need Your Help

ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

oracle oracle11g oracle9i

I'm trying to connect to a schema on an 11g (v11.2.0.1.0) from a PC with a 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a "ORA-01017 Invalid

objective-c: Conversion between TimeZones

objective-c nsdate nsdateformatter nstimezone

I'm having problems converting a time string to a different timeZone.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.