SQL Replace

The SQL Replace function replaces a value within a string with another value. The final output is the original string with the replaced value. There are three parameters to the Replace function. The first is the original string. The next parameter is the string to replace. And the last parameter is the value to replace the string with.

Here is an example:

[cc lang=”sql”]
SELECT
REPLACE
(
‘original string’, — Original String
‘string’, — String to replace
‘replaced’ — What to replace with
)
[/cc]

The output from this is: “original replaced”.

One thing you need to watch out for is that if any of the parameters in the replace statement are NULL, then the string that is returned will also be NULL. So if passing parameters dynamically and there is a chance of NULL, make sure to use the ISNULL function and turn it into an empty string.

You can also sometimes get away with using REPLACE rather than concatenating strings. I’ve done this before when you have a templated piece of text that needs values injected into it.

[cc lang=”sql”]
DECLARE @ResponseMessage varchar(max);

SET @ResponseMessage = ‘Dear [firstname], thank you for signing up for [subscription].’;

SELECT @ResponseMessage =
REPLACE
(
@ResponseMessage,
‘[firstname]’,
pc.FirstName
)
FROM Person.Contact pc
WHERE ContactID = 1;

SELECT @ResponseMessage;
[/cc]

Though it may not seem like much of a big deal, when dealing with many lines of concatenated text, it comes out much cleaner.