Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Replace() Function

The Replace() function can be used in the script or a chart to replace all occurrences of a sub-string with another sub-string within an input string.  Basically, you can replace a character or string of characters in a field or input string.  The syntax from Qlik Help looks like this:

 

Replace(text, from_str, to_str)

 

The first parameter, text, is a string.  This can be a field or some text.  The second parameter, from_str, is the string you would like to replace in the input string.  This can be a single character or a string of characters.  The third parameter, to_str, is the string that should replace the second parameter.  If the from_str is not found in the text then nothing is replaced.  The Replace() function works from left to right.

 

I have used the Replace() function when I have a field that has some characters that I do not need.  For example, I was loading names in a recent project and some of the names had a leading underscore character (_).  Since this was the case with only some of the names and not all, I used the Replace() function to replace the underscore with an empty string using syntax like this:

replace.png

You can also nest the Replace() function.  I have used this if there is more than one character I would like to replace in a string.  The nesting works from the inside out so keep this in mind as it may affect your results.  In the table below are a few examples of how Replace() can be used.

table.png

  • Column 1 - shows the original text string for Product.  This is the first parameter for all the Replace() examples.
  • Column 2 – replaces the underscore with a space.
  • Column 3 – is an example of a nested Replace() function.  First it replaces the underscore with an empty string.  Then it looks at the result of that string and replaces the question mark with an empty string.  The last step is to look at the results of that string and replace underscore question mark (_?) with a space (which will not be found in the Column 3 example).
  • Column 4 – is another example of a nested Replace() function but notice how the order affects the results for the input string “Product_?E”.  Since the first Replace() function to be evaluated replaces the “_?” with a space, the next two Replace() functions do not find the second parameter to replace.  Therefore, the result is “Product E” with a space before “E.”  In Column 3, the result is “ProductE,” without a space, since the underscore and the question mark were replaced individually with an empty string before the Replace(‘_?’, ‘ ‘) part of the expression was executed.

 

While my example shows how the Replace() function can be used in a chart expression, I always use it in the script to handle any replacements as I load the data.  What is nice about the Replace() function is it does not modify the text if the sub-string cannot be found.  With that in mind, you should make sure you want to replace all occurrences of the sub-string because there is not an option to pick and choose which sub-string occurrences to replace - all of them will be replaced.

 

Thanks,

Jennell

7 Comments
thiago_justen
Valued Contributor III

Great explanation jmc. But, if I'd like only purge some text from my string I can use the PurgeChar (). So here is my question: Considering this scenario, what's better: Replace() or PurgeChar ()?

For instance: I have this string - Text_ and I want replace _ to blank space. Should I use Replace or PurgeChar?

0 Likes
239 Views
anderseriksson
Contributor III

Exactly my thought also, why use Replace() when PurgeChar() can do the job?
Probably PurgeChar() is more efficient when dealing with single characters and with PurgeChar() you can specify several characters to purge individually. Using Replace() you would need multiple function calls to replace different characters. On the other hand Replace() is the one to use when you want to remove sequences of characters or when you actually want to replace characters with something else.

0 Likes
239 Views
Employee
Employee

Hi Thiago,

PurgeChar() may be the best to use if you want to remove characters versus Replace().

Jennell

0 Likes
239 Views
thiago_justen
Valued Contributor III

Got it Jennell. Thanks again!

0 Likes
239 Views
christian77
Valued Contributor

Good post!!!

Can you do that with numbers?

Thanks.

0 Likes
239 Views
Employee
Employee

Hi Christian,

Yes, you use Replace() with numbers too.

0 Likes
239 Views
thiago_justen
Valued Contributor III

christian77‌there is another option while you are using numbers:

Imagine that you want to keep only numbers from the string "id19023_us_0", for instance.

At this point you could use KeepChar('id19023_us_0','1234567890') -> output: 190230

The opposite of my example could be by using PurgeChar('id19023_us_0','1234567890') -> output: id_us_

More details: KeepChar()

                      PurgeChar()

Cheers

0 Likes
239 Views