Replace Multiple String Patterns

I recently came across an interesting way of using the REPLACE function in SQL Server to replace multiple (different) string patterns.

Usually, we use the REPLACE function in the following way if we want to replace multiple string patterns.

DECLARE @string AS VARCHAR(MAX) 
   = 'This is a banana. It may be found in the fruitbowl. Beside the banana, you may find an apple.';

PRINT REPLACE(
   REPLACE(
      REPLACE (
         REPLACE (@string, 'banana', 'hammer')
      , 'an apple', 'a screwdriver')
   , 'fruitbowl', 'toolbox')
, 'may', 'should');

The output is:

This is a hammer. It should be found in the toolbox. Beside the hammer, you should find a screwdriver.

However by storing the string_pattern & string_replacement in a table & adding a scalar variable, it is possible to replace all the string_patterns using a single REPLACE:

IF OBJECT_ID('tempdb..#replacement') IS NOT NULL
   DROP TABLE #replacement

CREATE TABLE #replacement (
   string_pattern VARCHAR(16),
   string_replacement VARCHAR(16)
);

INSERT INTO #replacement (
   string_pattern,
   string_replacement
)
VALUES ('banana', 'hammer'),
   ('an apple', 'a screwdriver'),
   ('fruitbowl', 'toolbox'),
   ('may', 'should');

DECLARE @string AS VARCHAR(MAX)
   = 'This is a banana. It may be found in the fruitbowl. Beside the banana, you may find an apple.';

-- Perform all replacements
SELECT @string = REPLACE(@string, string_pattern, string_replacement) FROM #replacement;

-- Return new string
PRINT @string;

Again, the output is:

This is a hammer. It should be found in the toolbox. Beside the hammer, you should find a screwdriver.

Interestingly, there doesn’t appear to be any documentation on this use of REPLACE and the execution plan simply shows a table scan of #replacement.