SQL REPLACE Overview and Examples

Problem

SQL Server Developers and Administrators occasionally need to perform string manipulations within queries or scripts. While there are several ways to accomplish these tasks, the SQL REPLACE function in Microsoft SQL Server was built just for this type of heavy lifting. Replacing all occurrences of a specified string value with another string value is the perfect use for this string function.

Solution

While looking at the SQL REPLACE function, it might look pretty simple, however, this can be a very powerful function and can help you in many different ways and is available back to SQL Server 2005.

Using the REPLACE() function will allow you to change a single character or multiple values within a string, whether working to SELECT or UPDATE data.

SQL Server REPLACE Function

In this first example let us examine the arguments available to the function.

Microsoft Documents sample syntax:

REPLACE ( string_expression , string_pattern , string_replacement )

string_expression – This is the string data you want the REPLACE() function to parse
string_pattern – This is the substring of character(s) to be found
string_replacement – This is the substring of character(s) to replace what was found i.e. replacement string

/* 
   Example: Simple REPLACE() Syntax
   we will replace . with !!!! 
*/
DECLARE @simpleString varchar(MAX) = 'Texas is the greatest state in the USA.'
SELECT REPLACE( @simpleString, '.', '!!!!')

Results:

---------------------------------------------------------------------------------------------------
Texas is the greatest state in the USA!!!!

SQL Server REPLACE Example: Simple Table Query

Let’s say someone said you need to change all SQL databases that start with DBA to start with SQLAdmin because of a security vulnerability.

This example will query the sys.databases table to find all databases that start with DBA and show what the new value will look like after applying the REPLACE function. After you see what the data looks like, you can take action to build additional SQL queries to rename the database from the original name to the new name.

/*
   Example: Simple Table Query
*/
SET NOCOUNT ON;
SELECT 
   name                             AS [Original Value], 
   REPLACE(name, 'DBA', 'SQLAdmin') AS [New Value] 
FROM sys.databases 
WHERE name LIKE 'DBA%'

Results:

Original Value                                 New Value
---------------------------------------------- ----------------------------------------------------
DBA                                            SQLAdmin
DBA_SchemaOnly                                 SQLAdmin_SchemaOnly
DBA_SchemaOnly_DBCC                            SQLAdmin_SchemaOnly_DBCC
DBA_SchemaOnly_Redgate                         SQLAdmin_SchemaOnly_Redgate
DBA_Steven                                     SQLAdmin_Steven

SQL Server REPLACE Example: Mask Data from a Table

Now it is time to do some string replacements for some real data for this example.

Your HR team comes to you and says the area code for all employees is changing from 987 (i.e. original string) to 123 (i.e. new string). See how this query can do this string manipulation to accomplish this task.

/*
   Example: Mask Data from a Table
*/
SET NOCOUNT ON
CREATE TABLE #PhoneNumbers(
   PhoneNumber VARCHAR(14)
)
INSERT INTO #PhoneNumbers VALUES('(987) 111-1111')
INSERT INTO #PhoneNumbers VALUES('(987) 222-2222')
INSERT INTO #PhoneNumbers VALUES('(987) 333-3333')
INSERT INTO #PhoneNumbers VALUES('(987) 444-4444')
INSERT INTO #PhoneNumbers VALUES('(987) 555-5555')
 
SELECT PhoneNumber AS [Original PhoneNumber], REPLACE(PhoneNumber, '987', '123') AS [New PhoneNumber] 
FROM #PhoneNumbers
 
DROP TABLE #PhoneNumbers

Results:

Original PhoneNumber New PhoneNumber
-------------------- ---------------
(987) 111-1111       (123) 111-1111
(987) 222-2222       (123) 222-2222
(987) 333-3333       (123) 333-3333
(987) 444-4444       (123) 444-4444
(987) 555-5555       (123) 555-5555

SQL Server REPLACE Example: Nested Replace Functions

There may be a need for you to replace multiple characters within one string.

This example will illustrate how to find three different strings in a single SELECT statement and replace the value. Here we will take out the ( ) and the – to have a non-formatted value for the phone number example.

/*
   Example: Nesting Replace Function
*/
SET NOCOUNT ON
CREATE TABLE #PhoneNumbers(
   PhoneNumber VARCHAR(14)
)
INSERT INTO #PhoneNumbers VALUES('(987) 111-1111')
INSERT INTO #PhoneNumbers VALUES('(987) 222-2222')
INSERT INTO #PhoneNumbers VALUES('(987) 333-3333')
INSERT INTO #PhoneNumbers VALUES('(987) 444-4444')
INSERT INTO #PhoneNumbers VALUES('(987) 555-5555')
 
SELECT PhoneNumber AS [Original PhoneNumber], 
       REPLACE(REPLACE(REPLACE(PhoneNumber, '(', ''), ')', ''),'-','') AS [Non-Formatted PhoneNumber] 
FROM #PhoneNumbers
 
DROP TABLE #PhoneNumbers

Results:

Original PhoneNumber Non-Formatted PhoneNumber
-------------------- -------------------------
(987) 111-1111       987 1111111
(987) 222-2222       987 2222222
(987) 333-3333       987 3333333
(987) 444-4444       987 4444444
(987) 555-5555       987 5555555

SQL Server REPLACE Example: Using CROSS APPLY versus Nesting Replace Function

The more you grow your query or complexity is added by nesting many REPLACE functions, the query can get difficult to write and challenging to read.

Instead of trying to struggle through translating massive nesting, you can use the CROSS APPLY to improve readability. Now we will convert our nesting example above to a CROSS APPLY to see how things look.

/*
   Example: Using CROSS APPLY versus Nesting Replace Function
*/
SET NOCOUNT ON
CREATE TABLE #PhoneNumbers(
   PhoneNumber VARCHAR(14)
)
INSERT INTO #PhoneNumbers VALUES('(987) 111-1111')
INSERT INTO #PhoneNumbers VALUES('(987) 222-2222')
INSERT INTO #PhoneNumbers VALUES('(987) 333-3333')
INSERT INTO #PhoneNumbers VALUES('(987) 444-4444')
INSERT INTO #PhoneNumbers VALUES('(987) 555-5555')
 
SELECT 
     op.PhoneNumber AS [Original PhoneNumber]
   , c.[non-formatted PhoneNumber] AS [non-formatted PhoneNumber]
FROM
   (SELECT PhoneNumber FROM #PhoneNumbers                            AS [Original PhoneNumber]) op
    CROSS APPLY(SELECT REPLACE(op.PhoneNumber,'(','')                AS [non-formatted PhoneNumber]) a
    CROSS APPLY(SELECT REPLACE(a.[non-formatted PhoneNumber],')','') AS [non-formatted PhoneNumber]) b
    CROSS APPLY(SELECT REPLACE(b.[non-formatted PhoneNumber],'-','') AS [non-formatted PhoneNumber]) c
 
DROP TABLE #PhoneNumbers

Results:

Original PhoneNumber non-formatted PhoneNumber
-------------------- -------------------------
(987) 111-1111       987 1111111
(987) 222-2222       987 2222222
(987) 333-3333       987 3333333
(987) 444-4444       987 4444444
(987) 555-5555       987 5555555

SQL Server REPLACE Example: Update Multiple Records

Your storage administrator comes to you and says that they just installed the best storage available. Your SQL Backup location is changing. All of your SQL Agent Backup jobs will need to have their backup path changed. We will use the REPLACE() function with the UPDATE statement to accomplish this with ease.

/*
   Example: Update multiple records
*/
SET NOCOUNT ON
SELECT sjs.command AS [Original Command], REPLACE(sjs.command, '\\oldslowstorage\sql\backups','\\FANCYNEWSTORAGE\database\backups') AS [New Command]
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs ON sjs.job_id = sj.job_id
WHERE sj.name LIKE 'DatabaseBackup%'
 
UPDATE sjs
SET sjs.command = REPLACE(sjs.command, '\\oldslowstorage\sql\backups','\\FANCYNEWSTORAGE\database\backups')
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs ON sjs.job_id = sj.job_id
WHERE sj.name LIKE 'DatabaseBackup%'