SQL Replace Function Explained [Practical Examples]

Overview on SQL Replace Function

SQL Replace is a built-in string function of SQL which is used to search and replace all occurrences of a substring within a given string with a new substring, this function searches for the substring in a case sensitive manner so we need to make sure to use the exact string that you want to search for and replace

SQL REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input

 

SQL Replace Function Syntax

REPLACE ( input_string_expression , string_to_find , string_to_substitute )

Here,

• Input_string_expression: It is the input string expression in which searching and replacement will perform.  input_string_expression can be of a character or binary data type
• string_to_find: It is the substring to be found. string_pattern can be of a character or binary data type. string_pattern must not exceed the maximum number of bytes that fits on a page. If string_pattern is an empty string (''), string_expression is returned unchanged
• string_to_substitute: It is the replacement string. string_replacement can be of a character or binary data type

 

SQL Replace function Return Types

SQL Replace function returns varchar data type value by default but if input string expression is of type nvarchar then function returns the value in nvarchar type,

SQL Replace function Returns NULL if any one of the arguments is NULL.

If string_to_find is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_to_find must be explicitly cast to a large-value data type

 

SQL Replace function Examples

We will use the following Table for all our examples in this article:

Student Table

Result Table

 

Example-1: SQL Replace function with the literal string

Write SQL Query to search and replace string with a string value in the given literal string expression ‘String: It is the expression or the string on which you want the replace () function to operate’

SELECT REPLACE ('String: It is the expression or the string on which you want the replace () function to operate', 'string', 'string value') AS 'Replace function'

In this query, SQL Replace function is used to replace the substring ‘string’ in the input expression string with ‘string value’ new substring value

OUTPUT:

 

Example-2: SQL Replace with Collate function

The following SQL uses the case-sensitive collation function to validate the expression within the SQL REPLACE function

Write SQL Query to replace string ‘2017’ to ‘2019’ in the given literal string expression ‘SQL SERVER Management Studio 2017’ with Collate function case sensitive value

SELECT REPLACE ('SQL Server Management Studio 2017' COLLATE Latin1_General_CS_AS, 'studio', 'express') AS 'Replace Collate'

• In the above query, COLLATE Latin1_General_CS_AS is used with SQL replace function to find substring and replace it with case sensitivity
• The output of the above query is a direct input of the expression as it fails to validate the input pattern

OUTPUT:

 

The following SQL uses the same example but case-insensitive collation function is used to validate the expression within the function

SELECT REPLACE ('SQL Server Management Studio 2017' COLLATE Latin1_General_CI_AS, 'studio', 'express') AS 'Replace Collate'

• In the above query, COLLATE Latin1_General_CI_AS is used with replace function to replace substring ‘studio’ with ‘express’
• The output shows the values are matched irrespective of cases, and replaced with a new substring value ‘express’

OUTPUT:

 

Example-3: SQL Replace with table column name

SQL replace function can also be used with column name to replace column value with new string, when column name is given as the input string, the search substring is searched in all the records of a table in that column and replaced with new substring

Write a query to format the semester name of the student by adding ‘-‘ between ‘sem’ and semester number

SELECT dbo.tblstudent.enrollmentno, dbo.tblstudent.studentname, REPLACE(dbo.tblresult.examname, 'sem', 'sem-') AS Semester
FROM dbo.tblresult LEFT OUTER JOIN
dbo.tblstudent ON dbo.tblstudent.student_id = dbo.tblresult.student_id

• In the above query, SQL Replace function is used to replace semester name with formatted text of semester name
• SQL Left outer join us used to join two tables student and result

OUTPUT:

 

Example-4: SQL Replace on numeric values with CAST function

SQL Replace is a string function that operates on string and text values, but we can also use SQL Replace function on numeric values by casting it into string data types using the SQL Cast function

Write a SQL query add ‘%’ sign at the end of the numeric value of the percentage column

SELECT dbo.tblstudent.enrollmentno, dbo.tblstudent.studentname, REPLACE(dbo.tblresult.pecentage, CAST(dbo.tblresult.pecentage AS varchar), CAST(dbo.tblresult.pecentage AS varchar) + '%') AS Semester
FROM dbo.tblresult LEFT OUTER JOIN
dbo.tblstudent ON dbo.tblstudent.student_id = dbo.tblresult.student_id

• In this query, SQL replace function is applied on the percentage column to add the ‘%’ sign at the end of the number value
• SQL replace function is work on string value so SQL Cast function is applied on both value to replace and new column value

OUTPUT:

 

Example-5: SQL Replace with an Update statement

So far in this article we have seen examples of SQL Replace function with literal strings and column value with Select statement so the replacement result will be displayed in the output, will not modify the column value, but we can use it to modify column values with SQL Update statement

Write SQL query to modify city column null values with city name Surat

update tblstudent set city=replace(city,' ' , 'Surat') ;

• In the above query, the SQL Replace function is used to modify the city column value in the table student
• The second argument is the NULL value which is going to replace with ‘Surat’

OUTPUT:

To see the output of the above query, SQL Select statement is used

SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM dbo.tblstudent

 

Write a SQL query to update the format of percentage with 2 floating point digits of .01

update tblresult set pecentage=cast(replace (pecentage, cast(pecentage as varchar),cast(pecentage as varchar) +'.01') as float)

• In this update statement query, SQL replace function is used to do formatting of percentage column values
• Here SQL Cast function is used on the argument of replace function as well as after formatting also cast function is applied on formatted values to convert it into float data types

OUTPUT:

To see the result of the above query we need to use the SQL Select command

SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status
FROM dbo.tblresult

 

Example-6: Nested SQL Replace function

We can use nested SQL Replace function to first replace the value and again replace it with another value

Example 6: write SQL query to replace domain name of email id with ‘@vnsgu.ac.in’ also replace the null value of emailed with department@vnsgu.ac.in string

SELECT REPLACE(REPLACE(email, 'gmail.com', 'vnsgu.ac.in'), ' ', 'department @vnsgu.ac.in') AS Email
FROM dbo.tblstudent

• In this query, nested SQL Replace function is applied on the emailID column, nested function is first to replace the domain mail of ‘gmail.com’ to ‘vnsgu.ac.in’ and then outer Replace function is used to replace the null value of emailID column to ‘department@vnsgu.ac.in’

OUTPUT:

 

Example-7: SQL Replace to Delete text from string

SQL replace function can also be used to delete text from the give string by replacing it with blank space

Use SQL Replace function to remove version name from given text’ SQL Server Management studio 2019’

SELECT REPLACE('SQL Server management studio 2019', '2019', ' ') AS 'Replace Delete Character'

In the above query replace function is used to remove substring 2019, the blank space is used as the replaced string argument

OUTPUT:

 

Example-8: SQL Replace with Regex pattern

Regular expressions are patterns used to match character combinations in strings, we can use regular expression Patten in the SQL Replace function to compare string and replace it with new string

REGEXP_REPLACE extends the functionality of the Replace function by letting you search a string for a regular expression pattern

Write SQL query to replace two or more spaces with a single space using regular expression as a search string

REGEXP_REPLACE('SQL Server  Management Studio   2019', '( ){2,}', ' ')  "REGEXP_REPLACE" FROM DUAL;

In this query regular expression ‘( ){2,}’ is used to search space and count occurrence of space, the patten {2, } check for 2 and more occurrence of blank space than replace it with single space

OUTPUT:

 

Summary

In this Article, the first overview of the SQL Replace function is covered with syntax and the explanation of each syntax argument, Return value datatypes of function, and particle example of Replace function with SQL Select statement on a literal string and numerical value, also with column name as the input string and in the ending section of article SQL Replace function with Update statement with practical examples has been explained

 

References

SQL  Coalesce function

 

Further Reading

SQL Replace