Replace() returns a string after replacing all occurrences of a given substring
within the input string with another
substring. The function is non-recursive and works from left to right.
Syntax:
Replace(text, from_str, to_str)
Return data type: string
Arguments
Argument
Description
text
The original string.
from_str
A string that
may occur one or more times within the input string text.
to_str
The string that
will replace all occurrences of from_str within the string text.
Example: Chart expression
Example
Result
Replace( 'abccde','cc','xyz' )
Returns abxyzde
Example - Replace fundamentals
Overview
A dataset of customer names contains some misspellings. This example uses the Replace function to process and replace any incorrect occurrence of 'Jhon' with 'John'.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
The following fields in the data table:
CustomerID
CustomerName
Load script
Example:
Load * inline [
CustomerID, CustomerName
1, Jhon Smith
2, Jhon Doe
3, John Williams
4, Jhonathan Harris
];
Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
CustomerID
CustomerName
Create the following calculated dimension:
=Replace(CustomerName,'Jhon','John'), to replace all instances of Jhon with John.
Results table
CustomerID
CustomerName
Replace(CustomerName,'Jhon','John')
1
Jhon Smith
John Smith
2
Jhon Doe
John Doe
3
John Williams
John Williams
4
Jhonathan Harris
Johnathan Harris
The output of the Repeat function finds and corrects all instances of Jhon with John.
Example - Replace scenario
Overview
A dataset of products uses inconsistent labeling for the category codes. For example, electrical products have multiple code variations, such as ELEC, ELC, and elc. To standardize the codes, the Replace function is used to correct all inconsistent category codes for both electrical and furniture products.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
The output of the Replace function has replaced any instances of ELC with ELEC, and FRN with FURN. Note that the code elc was also replaced. Although the Replace function is case-sensitive, the dimension formula uses the Upper function to conform the case of all CategoryCodes before replacing the string. This effectively renders the string replacement case-insensitive.
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.