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.

Advertisements

One comment

  1. BogStandardTSQL · · Reply

    See MSDN: http://msdn.microsoft.com/en-us/library/ms177523.aspx
    A variation on this is commonly used to concatenate rows to columns (as an alternative to FOR XML PATH())

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: