topic replacing characters within a string in QlikView

replacing characters within a string

Fri, 20 Nov 2009 18:53:30 GMT

Hi,

I have a field I am creating in the LOAD section of my script using various formulae.

I need to be able to interrogate a string and replace any instance of "%20" and replace with " ", for example.

Thanks in advance,

Matt

replacing characters within a string

Miguel_Angel_Baeyens — Fri, 20 Nov 2009 19:00:02 GMT

My untested guess is

Replace(Field,'%20',' ') AS Field

replacing characters within a string

Fri, 20 Nov 2009 19:03:41 GMT

Hi Matpj,

the function replace('abccde','cc','xyz') returns 'abxyzde' doesn't work for you need?

Best regards.

replacing characters within a string

rwunderlich — Sat, 21 Nov 2009 05:26:12 GMT

Replace() will work for the given example "%20", but if you have multiple sequences to translate, take a look at MapSubstring(). That will allow you build a table of substitutions and apply it in one pass.

Are you trying to unescape escaped strings like URLs? If so, an even easier way is to use the VB Script Unescape function. In your macro module, create a function like this:

Function un (str)
un = Unescape(str)
End Function

And then use it in your script like this:

un(url) as FixedUrl

-Rob