Chad Callihan neeeds to make an update:
Have you ever ran into a situation where you needed to replace both ends of a string? Maybe it was tags in a set of XML values or phrases at the beginning and end. I came across that situation recently and was able to show if two statements were needed or if REPLACE could be used against the same column in the same statement. Let’s look at a quick example and see what’s possible.
Chad’s solution is one I use fairly frequently. I agree that it’s not pretty, though one thing I like to do is tokenize code, so I might have something that looks like:
-- Procedure input parameters here
@FirstName NVARCHAR(150),
@FavoriteColor NVARCHAR(30)
-- Guts of procedure here
DECLARE @msg NVARCHAR(4000) = N'Hello, {FIRST_NAME}! Your favorite color is {FAVORITE_COLOR}!';
SELECT REPLACE(REPLACE(@msg, '{FIRST_NAME}', @FirstName), '{FAVORITE_COLOR}, @FavoriteColor);It’s definitely not the prettiest but when you have several tokens to replace, it’s a lot easier to read than building the string all at once.