Replace in SQL

What is Replace in SQL Server?

The REPLACE function in SQL String Functions is applied to replace all the occurrences of a substring with the new string. It is a built-in function provided by SQL. Therefore, instead of deleting the substrings manually, you can use the built-in function, i.e. replace in SQL to replace a substring within the other strings.

For example, if you want to replace the product name in the database, or there is a need to replace the dead link which is present in some of the records of the tables with the active link in the databases, you can use replace in SQL.

Syntax of Replace in SQL Server

The syntax of replacing in the SQL server is given below:

The above-given syntax defines how you can use the replace function in SQL, and there are parameters present in the replace function in SQL which are explained in the next section.

Parameter Values of Replace in SQL Server

The parameters present in the above syntax of replacing in SQL are explained below:

• string_expression:
  It is the string expression on which you want to apply the replace function in SQL. This string_expression can be of binary or character type.
• old_substring :
  The substring to be replaced i.e. the old_substring will be searched for in the string_expression. It can be of binary or character type.
• new_substring:
  It is the replacement string, i.e. the new substring that you want to place in all the occurrences of the old_substring. The new substring can be of binary or character type.

Return Types of Replace in SQL Server

• By default, the SQL replace function returns the varchar data type value.
• The replace function in SQL returns NULL if any of the arguments or the parameters of replace function in SQL is NULL.
• If the string_expression is of type nvarchar, then the SQL replace function returns the value of the type nvarchar.
• The return value is truncated at 8000 bytes by the REPLACE function in the SQL if the string_expression is not of type varchar(max) or nvarchar(max). Thus, to return the values greater than 8000 bytes, you need to explicitly cast the string_expression to a large-value data type.

Examples of Replace in SQL Server

Now, let's take some examples to understand how the REPLACE in SQL works.

Example - 1

A string_expression is provided, and you need to replace all the occurrences of the substring 'replace' with the substring 'REPLACE'. To achieve this, the below SQL query can be used.

Output:

In the above example, the first string is the string_expression, the second string is the string to be replaced, and the third string is the string with which the second string will be replaced in the string_expression. Remember that arguments in the replace function are case-sensitive, so only the "replace" string in the string_expression is replaced with the "REPLACE". Hence, the replace function will not consider 'repLACE' the same as 'replace'.

Example - 2

In this example, given the following string_expression, you need to replace all the occurrences of the 'JavaScript' with the string 'Python'.

Output:

In the above example, the string 'JavaScript' is replaced with 'Python'. Also, note that the 'Javascript' is not considered as the same as 'JavaScript'; hence the string 'Javascript' remains unchanged in the final result.

Example - 3

You can also use replace function in SQL to replace or correct the table columns. The below example gives a clear understanding of how you can replace the column data in the tables.

Consider the below employee table for this example,

Employee:

Output:

As shown in the above example, there are two replace functions in the SQL query. Let's see how the SQL query with the two REPLACE function works.

• The first REPLACE function i.e. REPLACE(Phone, '(', '') is applied to replace the character '(' with a space i.e. '' from the Phone in the Employee table.
• The second REPLACE function, i.e. the outer REPLACE function in SQL, uses the first replace function and replaces the character ')' with the '-' from the Phone column in the given Employee table.

Example - 4

Let's take one real-world example and a little advanced example to see how the replace function works in SQL. Suppose in the Employee table some of the initials of the phone number are wrong, and you have to replace those initials with the other number.

Output:

As you can see, all the records of Phone with '+91' were replaced with the '+92'. Hence, these are the mistakes in the database tables that can be easily and quickly solved with the help of replace function in SQL.