T-SQL's REPLACE() Function

DECLARE @VAR AS VARCHAR(50)
SET @VAR = 'DEF LEPPARD IS THE GREATEST ROCK BAND EVER!'
SELECT @VAR
-- USE W/ SET
SET @VAR = REPLACE(@VAR, 'DEF LEPPARD', 'METALLICA')
SELECT @VAR
-- USE W/ SELECT
SELECT REPLACE(@VAR,'METALLICA','LED ZEPPELIN')

-- LOOKS LIKE REPLACE() CAN ONLY BE USED IN EITHER A
-- SELECT STATEMENT OR WHEN SETTING A VARIABLE
-- http://msdn.microsoft.com/en-us/library/ms186862.aspx