EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 replace
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 replace

DB2 replace

Introduction to DB2 replace

DB2 replace is a function provided by IBM in the DB2 database management system which helps us to replace a particular string present inside the original string with any other required string. By using this function, all the occurrences of the mentioned string in the supplied string are being replaced. We can use this function in DB2 with string values, string literals, and even the fields of the columns that store the string values in them.

In this article, we will study the syntax of the replace() function in DB2, its usage, and implementation with the help of certain examples that will include replace() function used with string literals and some field values. The replace() function can be used in select as well as update query statements.

Syntax 

The syntax of the replace function is as shown below

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

REPLACE (main supplied string, string to be replaced, replace string value)

In the above syntax, the main supplied string is the original string that is passed to the function that consists of one or more occurrences of the string to replace. The string to be replaced is the unwanted string in the original string, in place of which we want to put some other value of the string. The replace string is the string with which the string to be replaced should be replaced. It is necessary to specify the main supplied string and string to be replaced. Replace string value is optional for the specification which when not specified the REPLACE() function evaluates to the same value as supplied as the main string. If any of the arguments to the REPLACE() function is passed as NULL then it will return the output value as NULL. In DB2, we mostly use the REPLCAE() function for cleaning up the data.

Examples

Let us have a look at how we can implement the REPLACE() function when using it with string literals. Consider one sample string say “Database Management System helps to maintain, store and retrieve the data from the database.”. In this original source string which will act as the main supplied string to our REPLACE() function, we have to replace all the occurrences of the database to space. Hence, our string to replaced is the “database” and the replace string with which we want to replace the “database” string is “space”. Hence, in order to retrieve the resultant replaced string, our query statement will become as shown below –

SELECT REPLACE('Database Management System helps to maintain, store and retrieve the data from the database.', 'database', 'space');

The output of the above-mentioned query statement is as shown below with all the occurrences of the “database” string being replaced by the “space” string in the original string –

DB2 replace output 1

Let us now consider an example of REPLACE() function with field values of the table. Suppose we have a table named Sales_Customers which stores all the data related to the sales done for each of the stores. The data related to each sale transaction along with the details of the customer is being recorded in this table.

Let us retrieve the records of that table by using the following select query –

SELECT * FROM [Sales_Customers];

The output of the above query statement is as shown below –

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,354 ratings)

DB2 replace output 2

Now, consider that we have to replace all the occurrences of the “sample” word in the “email_id” column with the “demo” word. This can be done by using the replace function with the source string value mentioned as the name of the field whose internal strings are to be replaced. In this case, the source string can be mentioned as email_id as the name of the column. Further the string value to be replaced will be “sample” and the string with which the value should be replaced is “demo”. Hence, our query statement will now become as shown below with and UPDATE query statement used instead of a SELECT query statement as we have to update all the row’s column values for email_id.

UPDATE
Sales_Customers
SET
email_id = REPLACE(email_id,'sample','demo');

The execution of the above query statements gives the following output on the screen affecting all the 14 rows present in it.

DB2 replace output 3

Let us check whether the change has been updated or not in the Sales_Customers table by executing the following select query –

SELECT * FROM [Sales_Customers];

The execution of the above query statement gives the following output as a resultant –

DB2 replace output 4

We can observe that the values in the column of email_id have been properly updated and the sample in the email id values has been replaced with a demo string.

Let us take one more example of a SELECT query statement, but this time we will consider a string field value and try to retrieve all the occurrences of a particular string in that string column with replaced values only for display purpose. However, internally the contents of the table should not get modified. Consider the same table Sales_Customers in which we will try to retrieve the values of the mobile number column but instead of 9145685 at the beginning of the number value we will replace it with ******* while retrieving. However, the stored values won’t be modified.

Our query statement for retrieving such kind of result will be as shown below –

SELECT customer_id, REPLACE(mobile_number, "9145685","*******") as "Encoded Mobile Number" FROM Sales_Customers;

The execution of the above query statement gives the following output with all the initial 7 characters of that mobile number modified to the * character in those places and displaying the last three characters numbers in it. We can recognize the customer from its customer_id field.

output 5

Let us once again confirm the values of the mobile_number and customer_id column in the Sales_Customers table to verify if the mobile numbers are still intact as they were original and are not being replaced by using the following query statement –

SELECT customer_id, mobile_number FROM Sales_Customers;

The execution of the above query statement gives the following output –

output 6

Conclusion – DB2 replace

We can use the REPLACE() function in DB2 provided by IBM to replace the string literal values. We can even replace the field values of the column and retrieve the replaced values keeping the content intact as well as if required update the replaced value permanently stored in that column with the help of the UPDATE statement.

Recommended Articles

This is a guide to DB2 replace. Here we discuss the syntax of the replace() function in DB2, its usage, and implementation with the help of certain examples. You may also look at the following article to learn more –

  1. DB2 Interview Questions
  2. What is DB2?
  3. Database Management Software
  4. Data Analysis Tools
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more