How to Use REPLACE in SQL

When querying data with SQL, the need to manipulate string-based results is very common. There are many options across the major relational database systems for this task. In this article, we will discuss the use case and functionality of the REPLACE SQL function.

Getting Started with REPLACE

The FIND and REPLACE functionality is well-known to everybody as it comes with any text editor. ANSI SQL offers the REPLACE functionality in the form of a built-in system function. In this article, I will be using SQL Server as my RDMS of choice.

REPLACE Function Parameters

REPLACE (string_expression, string_pattern, string_replacement)

String_expression is a string that will be transformed, it can be a single string or a column from an incoming SQL query.

String_pattern is a search pattern that is applied to the string expression to be replaced.

String_replacement is a replacement value applied to the string when the string _pattern is matched to the string_expression.

Let’s take a look at several examples to better understand the REPLACE functionality.

Creating Sample Data

For this example, I will create a temporary table to be used with the REPLACE function. The column names will match the parameter values of the function.

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


CREATE TABLE #TEMP_REPLACE_DEMO
(
ID INT IDENTITY(1,1),
STRING_EXPRESSION VARCHAR(2000),
STRING_PATTERN VARCHAR(200),
STRING_REPLACEMENT VARCHAR(200)
)

--REPLACE THE VALUE CAR WITH FOX
INSERT INTO #TEMP_REPLACE_DEMO
VALUES('The red car is the fastest car in the race.','car','fox')

--REPLACE THE VALUE 4 WITH 8
INSERT INTO #TEMP_REPLACE_DEMO
VALUES('There are 4 model ford cars in the parking lot.','4','8')

--REMOVE THE BLANK SPACE CHARACTER
INSERT INTO #TEMP_REPLACE_DEMO
VALUES('    This sentence    has     irregular spacing              .       ',' ','')

Simple and Dynamic REPLACE Examples

Now that the table is loaded with some sample data, let’s examine how the REPLACE function can be applied to the data at a basic level and then in a more dynamic capacity.

In the first example, the parameters are hard-coded as string values to the function.

--REPLACE THE VALUE CAR WITH FOX
SELECT REPLACE('The red car is the fastest car in the race.','car','fox') AS RESULT_FOR_SENTENCE

The values can also be read from the table dynamically. In this case, the expression, pattern, and replacement values are all read from the table directly instead of being hard-coded.

--DYNAMIC EXAMPLE FOR ALL COLUMNS IN THE TABLE.
SELECT ID,REPLACE(STRING_EXPRESSION,STRING_PATTERN,STRING_REPLACEMENT) AS RESULT_FOR_TABLE
FROM #TEMP_REPLACE_DEMO

The results for ID 1 are the same as the example above, the only difference is that the values were read from the individual columns.

Nested Replacements

The REPLACE function can also be used in a nested capacity. This means that the results of the inner REPLACE function can become the STRING_EXPRESSION parameter of the outer replacement function.

Here are some examples of this pattern on the sample data:

--REPLACE THE VALUE CAR WITH FOX | REPLACE THE VALUE "fastest" WITH "slowest"
SELECT REPLACE(REPLACE('The red car is the fastest car in the race.','car','fox'),'fastest','slowest')


--REMOVE THE BLANK SPACE CHARACTER | REPLACE THE VALUE "irregular" with "no"
SELECT REPLACE(REPLACE('    This sentence    has     irregular spacing              .       ',' ',''),'irregular','NO')

Collation and REPLACE in SQL

Collation is an important factor to consider when working with string data and the REPLACE function. Collation can be set at multiple levels of a database such as an account or instance level, the database, schema, session, or table level. This setting can affect how the REPLACE function works.

In this example, you may not expect capital A to be replaced with lowercase b. But it is dependent on the collation which is being inherited to the current connection to the database.

--REPLACEMENT WITHOUT EXPLICIT COLLATION.
SELECT REPLACE('A','a','b') AS DEFAULT_COLLATION

This result matches a to A because of the collation.

The collation setting in SQL Server can be checked at the server level:

--VIEW CURRENT COLLATION
SELECT CONVERT (varchar(256), SERVERPROPERTY('collation')); 
 

The key here is the CI which stands for case insensitive. To apply the REPLACE function in a case-sensitive capacity, the string_expression parameter can be collated explicitly.

-- USE COLLATE TO MATCH CHARACTER CASING FOR REPLACE
SELECT REPLACE('A' COLLATE Latin1_General_CS_AS ,'a','b') as Explicit_Collation;

In this example, because the string_expression parameter is collated as case sensitive, the REPLACE function does not replace the original value.

Summary

The REPLACE function is a great tool for basic and advanced string manipulation across multiple relational database systems. This article examined the basic application of the REPLACE function, how to use it in a dynamic capacity. We also discussed using REPLACE as a nested function, and how collation can affect the results returned by it.