RnewGrab Deal : Flat 23% off on live classes + 2 free self-paced courses as a bonus! - SCHEDULE CALL Rnew

- SQL Server Blogs -

SQL Replace Function - All You Need to Know



Introduction

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 or Replace SQL Server or the Replace function in SQL 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.

Read: How To Become Expert In Sql Server Developer?

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

Let’s get started with SQL Server training demo

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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 if 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 or Replace SQL function with T-SQL constructs.

Read: Step By Step SSAS Tutorial For Beginners

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 Server 2019

In SQL Server 2017 or higher (say it SQL Server 2019), you can use the 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.

Remember everything with SQL Server online training

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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. 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: How To Quickly Get An Entry Level SQL Jobs?

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.

Read: How to Increase the Speed of SQL Query Execution

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: SAS Tutorial Guide for Beginners

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. 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;

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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 Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key

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.

Conclusion

Being a database coder, you can apply data intelligence in SQL Server using SQL replace or Replace SQL or the other query statements.To know more on different SQL string functions and how they are used within a query, you may join SQL certification course at JanBask training and practice multiple problems to gain hands-on expertise on multiple SQL statements.

Read: How To Quickly Get Entry Level SQL Jobs?

SQL Tutorial Overview

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

AWS Course

AWS

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

Upcoming Class

1 day 31 Mar 2023

DevOps Course

DevOps

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

Upcoming Class

0 day 30 Mar 2023

Data Science Course

Data Science

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

Upcoming Class

1 day 31 Mar 2023

Hadoop Course

Hadoop

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

Upcoming Class

1 day 31 Mar 2023

Salesforce Course

Salesforce

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

Upcoming Class

1 day 31 Mar 2023

QA Course

QA

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

Upcoming Class

1 day 31 Mar 2023

Business Analyst  Course

Business Analyst

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

Upcoming Class

8 days 07 Apr 2023

MS SQL Server Course

MS SQL Server

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

Upcoming Class

8 days 07 Apr 2023

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

2 days 01 Apr 2023

Artificial Intelligence  Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence  Course

Upcoming Class

16 days 15 Apr 2023

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

8 days 07 Apr 2023

Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
Tableau Course

Upcoming Class

9 days 08 Apr 2023

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews