SQL Server Replace Function + Examples

In this SQL Server tutorial, we will learn about SQL Server Replace, Different examples on SQL Server Replace, and cover the below topic.

• SQL Server Replace Function
• SQL Server Replace multiple characters
• SQL Server Replace special character
• SQL Server Replace text in column
• SQL Server Replace newline character
• SQL Server Replace empty string with value
• SQL Server Replace null with 0
• SQL Server Replace single quote
• SQL Server Replace blank with null
• SQL Server Replace double quotes with single
• SQL Server Replace accented characters
• SQL Server Replace string after a character
• SQL Server Replace string between two characters
• SQL Server Replace all occurrences of a string

Here I am using sql server 2019 express with Sql server management studio.

SQL Server Replace Function

In SQL Server, replace is a function, which is used to replace a substring in a given string with another substring. And it returns a new string in which the new substring replaces all occurrences of the substring.

The Replace() function in SQL Server accepts 3 arguments and it has the following syntax.

REPLACE( string_expression , string_pattern , string_replacement )  

• The string_expression in the above syntax is a character or binary data type expression, which is to be searched.
• The string_pattern is used to represent the substring, which a user wants to replace from string_expression with a new substring. The string_pattern can be a binary or character data type expression, and if string_pattern is defined as an empty string (”), the string_expression will be returned unchanged.
• The string_replacement, in the above syntax, is used to represent the new substring that a user wants to replace instead of string_pattern(old substring). It is also a character or binary data type expression.
• The Replace() function will return NULL if any of the above arguments is NULL.

SQL Server Replace Function Example-1

SELECT 
    REPLACE(
        'I want a burger.', 
        'burger', 
        'coffee'
    ) Result;

In the above example, we have defined the string_expression as ‘I want a burger.‘ within the Replace() function. After this, we have defined the string_pattern as ‘burger‘ and then we have defined the string_replacement as ‘coffee‘.

So the Replace() function will first try to find the “burger” substring in the “I want a burger” string. After this, it will replace the “burger” substring with the “coffee” substring. And in the end, it will return the new string.

Read SQL Operand data type real is invalid for modulo operator

SQL Server Replace Function Example-2

SELECT 
    REPLACE(
        'James is from Canada.', 
        'Canada', 
        'United States of America'
    ) Result;

In the above example, we have defined the string_expression as ‘James is from Canada.‘ within the Replace() function. After this, we have defined the string_pattern as ‘Canada‘ and then we have defined the string_replacement as ‘United States of America‘.

So the Replace() function will first try to find the “Canada” substring in the “James is from Canada.” string. After this, it will replace the “Canada” substring with the “United States of America” substring. And in the end, it will return the new string.

Read: SQL Server Convert String to Date + Examples

SQL Server Replace text in column

We can also use the Replace() function to update or edit column values in SQL Server. For this, we can use the following syntax.

UPDATE 
    table_name
SET
    column_name = REPLACE(column_name, 'old_string','new_string')
WHERE
    condition;

Let’s understand this with the help of an example. Now for demonstration, consider the following sample table in SQL Server

The above table contains a Contact column and each contact value consists of a number that follows a particular format (eg-xxx-xxx-xxxx). So for this example, we are going to replace the ‘-‘ hyphen in the contact number with space ” “. And for this, we are using the following query.

UPDATE
    Sample
SET
    Contact = REPLACE(Contact,'-',' ')
WHERE
    Contact IS NOT NULL;

In the above query, we are updating the Sample table and we are changing the Contact column values using Replace() function. And in the Replace() function, we have used Contact column as input expression, and we are replacing the hyphen (-) with blank space (” “) . After updation our will look like this.

Read: SQL Server Convert Datetime to String

SQL Server Replace null with 0

In SQL Server, there are 2 ways through which we can replace a NULL value with ‘0‘. And we are going to discuss both the ways with the help of an example below.

Using COALESCE() function

The Coalesce in SQL Server is a function that accepts multiple expressions as an argument, and evaluates each argument in order, and returns the first not-null expression in it. It has the following syntax.

COALESCE( expression [ ,...n ] ) 

The Coalesce() can accepts multiple expression of any data type.

Now let’s understand its implementation with the help of a small example. For this, consider the following Score column which also contains NULL values.

Now we will try to replace NULL with ‘0‘ in Score column by using following query.

UPDATE
    Sample
SET
    [Score] = COALESCE(Score, 0)

In the above query, we are using the UPDATE clause to edit the Score column. And we are using the Coalesce() function to set values for the Score column. The Coalesce() function will evaluate the Score column and return 0 instead of NULL as 0 is the first not-null value in Coalesce function.

Using ISNULL() function

in SQL Server, ISNULL is a function that replaces the NULL value with a specified expression. And it has the following syntax.

ISNULL( check_expression , replacement_value )  

• The ISNULL() function has 2 parameters, first is check_expression which represents the expression that is needed to be evaluated for a NULL value. The ISNULL() function will check the check_expression for NULL values.
• The second parameter is the replacement_value which represents the expression that has to be replaced with NULL.
• if the check_expression is not null then the function will return check_expression only.

Again for demonstration, we are going to use the same Score column example. But this time, we are going to use the ISNULL() instead of Coalesce().

UPDATE 
    Sample
SET
    [Score] = ISNULL(Score,0)

In the above query, we are using the ISNULL() function to replace all the NULL values with 0 in Score named column.

Read: How to create functions in SQL Server Management Studio

SQL Server Replace multiple characters

While working with raw data in SQL Server, we may need to format data and we may need to replace multiple characters within one string. Now let’s take an example to understand this implementation.

For demonstration, consider the following Contact column. And the contact numbers follows a particular format [“(xxx) xxxxxxx”] but for our work, we require to contact in (xxx xxxxxxx).

Now we will try to replace ‘(‘ and ‘)‘ characters with an empty string by using the following query.

SELECT Contact AS [Original ContactNumber], 
       REPLACE(REPLACE(Contact, '(', ''), ')', '') AS [Formatted ContactNumber] 
FROM Sample

We can easily replace multiple characters in SQL Server by using multiple Replace() functions in one another. In this example, we want to replace 2 characters [ ‘(‘ and ‘)‘] so we are using 2 Replace() functions. The Replace() function from the last will be executed first, and its result will be used as input for another Replace() function.

After successful execution, it will return the following result.

SQL Server Replace special character / ASCII characters

One of the most critical processes in an ETL process is data clean-up, which requires removing trailing and leading spaces. However, eliminating ASCII special characters like newline characters and horizontal tab might be difficult.

The American Standard Code for Information Interchange (ASCII) is a standardized numeric code for encoding character data in a computer that is used widely. Additionally, SQL Server utilizes ANSI which is an upgraded version of ASCII. And SQL Server also includes a built-in CHAR function for converting an ASCII numerical value back to its original character or symbol.

Hence, we can easily fetch a special character by using ASCII numeric code in CHAR() function as shown in the example.

Here is a list of some special characters with there ASCII code.

Code	Character	Name
33	!	Exclamation Mark
34	“	Double Quote
35	#	Number Sign
36	$	Dollar
37	%	Percent
38	&	Ampersand
39	‘	Single Quote
40	(	Parenthesis
41	)	Parenthesis
43	+	Plus Sign
92	\	Backslash
149	•	Bullet
153	™	Trademark Symbol
174	®	Registered trademark
183	·	Interpunct

So now let’s understand how we can replace any special keyword using the Replace() function in SQL Server. And for example, consider the following sample table.

The table above consists of an email column that carries an unformatted email address. And each email value contains an extra exclamation Mark (!) and a dollar sign ($).

Now we are going to remove the Exclamation Mark (!) and a dollar sign ($) using the Replace() function.

SELECT email, REPLACE(REPLACE(email, CHAR(33), ''), CHAR(36), '') 
AS [Formatted email]
FROM Sample;

In the above query we have used 2 Replace() functions to first remove exclamation Mark (!). And then we are using its result as an input for another Replace() function that is used to remove the dollar sign ($) from the email column.

After execution, the query will return the following result.

Read: SQL Server Convert Datetime to date

SQL Server Replace newline character

We can easily replace a new-line or line break character with any other character by using Replace() function. And in Replace() function for specifying the new-line character, we can use ASCII code for the new-line character in Char() function.

Now, for example, consider the following string from which we are going to replace the new line character with a comma (,).

DECLARE @strInput VARCHAR(100)
SET @strInput ='January
February
March
May
June
July
August
September
October
November
December'
Select @strInput as Input

Now to replace newline character with comma (,) we are going to us the following query.

SELECT REPLACE(@strInput,CHAR(13)+CHAR(10),', ') AS 'New String'

In the above query, we are using the Replace() function within the SELECT statement. And we have also given our string variable as an input expression in Replace() function. After this, we are using 2 Char() functions, Char(13) for carriage return, and Char(10) for new-line and we replace this with a comma (, ).

SQL Server Replace empty string with value

Now to replace an empty string with a value in SQL Server is a bit tricky as we cannot use Replace() or Coalesce() function directly for it. For this implementation, we have to use NULLIF() function with Coalesce().

The NULLIF() function takes two expressions and returns NULL if both the expressions are the same. So we are going of use NULLIF() function to find an empty string in an expression and then using Coalesce() we are going to replace an empty string with a value.

Now for demonstration consider the following sample table.

The gender column, in the above table, is containing an empty string instead of a ‘Male’. So we are going to use the following query to replace an empty string with a ‘Male’ string value.

Update Sample
Set
gender = COALESCE(NULLIF(gender,''), 'Male')
WHERE gender=''

In the above query, we are using the NULLIF() function within Coalesce() function, which will return NULL for each empty string found in the gender column. And by using Coalesce(), we are replacing all the empty strings with the ‘Male’ string.

After successful execution, all the empty strings in the gender column will be replaced by Male.

Read: SQL Server Convert Function

SQL Server Replace single quote

In SQL Server, to get a single quote expression(‘), we can use the ASCII character code for a single quote in Char() function. And the Char() function will return the single quote as an expression.

From the above example, we get to that the ASCII code for a single quote character is 39. So we can use the Char() function within Replace() function to replace any other value with a single quote.

SELECT first_name, REPLACE(first_name,CHAR(39),'') FROM Sample

In the query above, we have given the first_name column as an expression to be searched in Replace() function. After this, we have defined CHAR(39) as a character to be replaced with an empty string. So after execution, it will replace all the single quotes with an empty string first_name column.

SQL Server Replace blank with null

In SQL Server, a blank or an empty expression is represented by two single quotes (”). Now to replace a blank value with NULL, we can use the Replace() function, which is used to replace character expression in a given string expression.

For demonstration, consider the following sample table which contains black values in the “Company Name” column.

Now we are going to use the following query to replace black values with null in “Company Name” column.

UPDATE Sample
SET
[Company Name] = REPLACE([Company Name],'',NULL)
WHERE [Company Name]=''

In the above query, we are using Replace() function to change the blank or empty values(”) with NULL in the Company Name column. And after this query execution, our table will be updated.

SQL Server Replace double quotes with single

In SQL Server, we can replace double quotes with a single quote by using the Replace() function. And to define the double and single quotes in Replace(), we can use Char() function.

DECLARE @strInput VARCHAR(100)
SET @strInput ='"SQLServerGuides"'
SELECT @strInput AS 'Old String',
       REPLACE(@strInput,CHAR(34),CHAR(39)) AS 'New String'

• So in the above code, first, we have defined a varchar variable as “SQLServerGuides”, and we are using double quotes within it.
• After this, we have used Replace() function within the SELECT statement to replace the quotes.
• For double quotes, we are using CHAR(34) and for single quotes, we are using CHAR(39) function.

Afer execution, it will return te following result.

SQL Server Replace accented characters

Now as a database developer, there can be scenarios where you have accented character in your data, and you want a normal character in place of it. For this purpose, we can use Replace() function with Collate command in SQL Server.

Let’s understand this with an example, and consider the following sample table.

In the above table, the Name column contains the â character, and to replace it with a normal character, we are going to use the following query.

SELECT Name, REPLACE(Name COLLATE Latin1_General_CS_AI, 'a', 'a') 
AS 'Updated Name' 
FROM Sample

In the above query, we are using the Replace() function within the SELECT statement. And in Replace() function, we have defined the Name column as our expression to be searched. And with name, we are using COLLATE command which defines a set of collections. Now by using the collection name, the â character can be replaced with normal a character.

The query will return the following output.

Read: SQL Server Substring Function [9 Examples]

SQL Server Replace string after a character

In SQL Server, we cannot directly replace a substring after a certain character using Replace() function. A basic approach for this implementation can be as following

• First, we should use CHARINDEX() function to find the index value of a certain character, after which we want to replace a string.
• Next, we should use the Substring() and Len() functions to extract the substring after the certain character.
• In the last, we should use the extracted string in Replace() function to replace it with a new substring.

Now let’s put all the points together and implement this by using the following query.

SELECT [NAME], 
        REPLACE([NAME],
		SUBSTRING([NAME],CHARINDEX(',',[NAME])+1,
		LEN([Name])),'') 
	AS [First Name] 
FROM Sample

In the above query, we are using the Name column of a table named Sample. The Name column contains the full name of an employee and there is a comma (,) between the first and last name. And we are trying to replace all the characters after comma (,) with an empty string using this query.

After execution, the query will return the following result.

SQL Server Replace string between two characters

So similar to replacing a string after a certain character, we cannot directly use the Replace() function directly to replace string between two characters. But a fundamental approach to achieving this can be as follows.

• First, use the Charindex() function to find the position of both characters in a given expression.
• Then, use those position values in the Substring() function to extract the substring between those characters.
• In the last, use the extracted substring in Replace() function to replace it with a new substring.

Now for demonstration, we are going to replace the substring between ‘@’ and first ‘.’ in an email address with a new substring. And for this, we are going to use the following query.

SELECT [email], REPLACE([email],
	SUBSTRING([email],
		CHARINDEX('@',[email])+1, 
		CHARINDEX('.', [email])-CHARINDEX('@', [email])-1),
	'gmail') 
AS [Updated Email] 
FROM [Sample]

• In the above query, we are using the Replace() function within the SELECT statement. In the Replace() function we have defined the email field as the expression to be searched.
• After this, we have used a Substring() function. In Substring() function we have used 2 Charindex() functions to extract the substring between ‘@’ and ‘.’.
• In the end, we are using the extracted substring to be replaced with the ‘gmail‘ substring.

It will give the following output.

SQL Server replace all occurrences of a string

The REPLACE() function, in SQL Server, can be used to replace all instances of a given string from another string. Let’s understand this with the help of an example.

Here is our sample string, “I went to a tea shop to have a cup of tea“. The string carries the ‘tea’ word two times and we are going to use the following query to replace ‘tea’ with ‘coffee’ for both occurrences.

DECLARE @substring varchar(60) = 'I went to a tea shop to have a cup of tea'
SELECT @substring AS [OLD_String], REPLACE(@substring,'tea','coffee') 
AS [NEW_String]

In the above query, we are using the Replace() function to change the ‘tea‘ substring with ‘coffee’. And after execution, it will return the following result.

In this tutorial, we have learned about SQL Server Replace, Different examples on SQL Server Replace, and cover the below topic.

• SQL Server Replace
• SQL Server Replace multiple characters
• SQL Server Replace special character
• SQL Server Replace text in column
• SQL Server Replace newline character
• SQL Server Replace empty string with value
• SQL Server Replace null with 0
• SQL Server Replace single quote
• SQL Server Replace blank with null
• SQL Server Replace double quotes with single
• SQL Server Replace accented characters
• SQL Server Replace string after a character
• SQL Server Replace string between two characters
• SQL Server Replace all occurrences of a string