Multiple REPLACEments in one view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple REPLACEments in one view

Author

Topic

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-10-01 : 06:51:52

My table looks like this
Product Code
NR NR
NR WV
PP PP
PP WV
PR LF
PR LV
PR R3
PR R6
PR WV
PT PT
PT WV

I want to replace WV with new codes. The problem is that each WV has a different new code for each product.

By using;
SELECT REPLACE(Code, 'WV', 'WVP') AS Expr1
FROM dbo.tablename
WHERE (Product = 'PR')

replaces WV with WVP where the product is PR. How do i then replace WV with PPWV when the product is PP on the same view. And then replace WV with FRPWV when the product is NR.

The aim is to get the view to look like the table below.

Product Code
NR NR
NR FRPWV
PP PP
PP PPWV
PR LF
PR LV
PR R3
PR R6
PR WVP
PT PT
PT WV

Any ideas please?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-01 : 07:19:22

You can use CASE statement and base on the Product, you can replace the code.

Something like:
Case
when product ='PR' then REPLACE(Code, 'WV', 'WVP')
when product ='PP' then REPLACE(Code, 'WV', 'PPWV')
...

End as expr1

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-10-01 : 07:33:36

How do i start the view. SELECT CASE? do i not need a FROM?

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-10-01 : 07:41:40

Where have I gone wrong here?

SELECT
CASE pk_bhist
WHEN pk_product = 'PR' THEN REPLACE(pk_benefit, 'WV', 'WVP')
WHEN pk_product = 'PP' THEN REPLACE(pk_benefit, 'WV', 'PPWV')
WHEN pk_product = 'NR' THEN REPLACE(pk_benefit, 'WV', 'FRPWV')

END

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-10-01 : 07:43:24

Sorry, meant to paste this one.

SELECT
CASE tablename
WHEN product = 'PR' THEN REPLACE(code, 'WV', 'WVP')
WHEN product = 'PP' THEN REPLACE(code, 'WV', 'PPWV')
WHEN product = 'NR' THEN REPLACE(code, 'WV', 'FRPWV')

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 07:43:25

select
Case
when product ='PR' then REPLACE(Code, 'WV', 'WVP')
when product ='PP' then REPLACE(Code, 'WV', 'PPWV')
...more WHEN/THEN...
ELSE Code
END as Expr1,
*
from table

No, you're never too old to Yak'n'Roll if you're too young to die.

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-10-01 : 07:47:53

It worked. Much Appreciated!!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 07:53:25

welcome