Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

All you need to know about SQL Replace Function

When you are working with databases, you want to search or replace an existing substring with the new one. For example, change the dead link to the new one or renaming the product name with a new string as given. SQL provides a quite useful function REPLACE () to update the content of a string. It allows you to replace all occurrences of a substring with a new substring. Keep in mind that search is always case-sensitive and the REPLACE function performs the comparison based on collation of input expressions.

The general syntax for REPLACE () function in SQL server is given as: REPLACE(string, old_substring, new_substring);

  • String: The original string you want to search and update. It can be of a character or binary data type.
  • Old-substring: The string to be replaced. It should not be empty. It should not exceed the maximum length that fits on the page.
  • New-substring: This is the new replaced string that can be of a character or binary data type.

Example: SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML'); In this example, we want to replace the SQL with HTML in the original string “SQL Tutorial”. The final output will be “HTML Tutorial” when you run this SQL query. If the function does not find the string, it does nothing. It will return the original string in that case if it does not find any occurrences of the substring.

HTML Tutorial SELECT REPLACE('ABC ABC ABC', 'a', 'c'); In this example, we want to replace the “A” with “C” in the original string “ABC ABC ABC”. The SELECT statement finds the rows or columns first that will be affected by the replacement. When you run this SQL query, the final output will be: cBC cBC cBC

Return Types

  • If the input value is of varchar datatype then the string function will return varchar otherwise it may return nvarchar.
  • It returns NULL is any of the arguments is NULL.
  • If the string expression is not of type varchar (max), REPLACE the return value at 8000 bytes. To return the value greater than 8,000 bytes, it should be casted explicitly to a large-value data type.

SQL REPLACE Use Cases

Generally, when you migrate a database, the dependent objects also need to be migrated. Let us discuss in little more depth about the process of handling such changes dynamically using SQL REPLACE function with T-SQL constructs. SQL Server Curriculum For one database migration project, there are multiple linked server configurations. The linked servers are mentioned or referred to in multiple stored procedures. It is a matter of fact to find or update the stored procedure scripts but the intent is to automate the complete process and make sure that there is no need for manual updates.SQL REPLACE Use Cases

  • In this example, the search pattern is “employee” and also use the custom escape characters to escape the special characters in the search pattern. Here 13 objects are listed to satisfy the search condition employee.SQL REPLACE Use Cases
  • Now use the loop to go through the listed objects and generate the script and store the result in the temp table.
  • Perform a simple update to change the keyword from CREATE to ALTER. In this way, the script is ready to execute on the target database. In a few cases, there is a need for retaining script.SQL REPLACE Use Cases
  • This is the right time to copy and paste the new query window and make sure that everything looks fine to run the SQL. You may also automate the process using stored procedures in the SQL.

Translate vs Replace functions in SQL 2017

In SQL Server 2017, you can use TRANSLATE function to translate one or more characters into a complete set of characters. At this glance, REPLACE and TRANSLATE functions exactly do the same thing but there are significant differences too.

  • REPLACE () function replaces all occurrences of a specified string value with another string value.
  • For the TRANSLATE () function, it returns the string provided as the first argument after some characters provided in the second argument are translated to the destination set of characters.

The major difference is how both functions deal with multiple characters. REPLACE function replaces one string with another string, so string should always be in the same order to replace. At the same time, TRANSLATE function works on characters and replaces each character one by one, regardless of their order. SQL Server quiz Let us understand the concept in depth with the help of example below. In the first example, both functions will return the same output and the query looks like this. SELECT REPLACE('123','123','456') AS Replace, TRANSLATE('123','123','456') AS Translate;

Read: SQL Server Tutorial for Beginners

The output for this query will be the same as given below. Replace     Translate -------          --------- 456               456

In this case, REPLACE function returns 456 because the value in the second argument matches exactly with the first argument. The TRANSLATE function also returns the same value i.e. 456 because each character in the second argument is available in the same order as of the original one. Let us write one more query where both functions return the different values. It will help you to understand how both functions are different.

SELECT REPLACE('123','321','456') AS Replace, TRANSLATE('123','321','456') AS Translate;

The output for this query will be different as given below.

Replace     Translate -------          --------- 123               654

For the REPLACE function, the second argument does not match the value in the first argument in the same order, so it will not replace the string but return the original string without performing any operation on it.

For the TRANSLATE function, it will work on character instead of the whole string. As all characters in the second argument are available in the original string too, so the final output is processed as 654 in this case.

Moving ahead, let us see how these functions work for the non-contiguous strings. Similar to the previous example, you will get the different result but the string is non-contiguous here.

Read: Job Roles and Responsibilities of a SQL Server Developer you Need to Know

SELECT REPLACE('1car23','123','456') AS Replace, TRANSLATE('1car23','123','456') AS Translate; The output for this query is given below.

Replace     Translate -------          --------- 1car23               4car56

Here, the REPLACE function will return the original string without performing any update while TRANSLATE function works on characters and replaces values as given in the output. With these examples, you must be sure now how REPLACE and TRANSLATE functions work in SQL. You can quickly decide which fits your requirements the most. free SQL Server demo There is one more example where arguments are of different length or we can say there is a discrepancy in the number of characters for various arguments. Here, the first argument contains fewer characters when compared to the second argument.

SELECT REPLACE('123','1234','4567') AS Replace, TRANSLATE('123','1234','4567') AS Translate;

The output for this query will be: Replace     Translate -------          --------- 123               456

In the first case, REPLACE function returns the original string while the TRANSLATE function will replace the string based on characters value instead of looking at the length of parameters. For the TRANSLATE function, it does not matter either second argument contains more characters or not. The most important thing is that characters should be the same as of the original string. There are some cases when REPLACE function works more suitably and TRANSLATE function throws an error. In this example, the second argument has a smaller number of characters than the original string, so REPLACE function works suitably here.

SELECT REPLACE('1234','123','4567') AS Replace;

The output for this query will be:

Read: What is SQL Formatter? Features of SQL Formatter

Replace ------- 45674

Here, REPLACE function works in the same way as it is expected but TRANSLATE function will throw an error. SELECT TRANSLATE('1234','123','4567') AS Translate; The output for this query will be:

Error: The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters. Final Words:

You should use the REPLACE function when you want to update all the occurrences of a specified string exactly in the same order as it is written. And the TRANSLATE function is useful if you want to replace the occurrence of each character specified, regardless of their order within the specified string.

To know more on different SQL string functions and how are they used within a query, you may join SQL certification course at JanBask training and practice multiple problems to gain hands-on expertize on multiple SQL statements.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews