How to Replace Substring Based on Wildcards

[Øystein_Kolsrud]

I would like to replace a sub string based on a wildcard match. How would I do this? The function "Replace" does not seem to accept wildcards, but conceptually I would like to be able to do something like this:

Replace(MyField, 'b.d', 'XYZ'))

Then examples of field values and results results would look like this:

Field Value	Result
abcde	aXYZe
abfde	aXYZe
abde	abde
axcde	axcde

[bramkn]

so only bd get replaced when there is something in the middle of it?

I think you will need to use the substring/index function and concatenate the XYZ in the middle of it deleting bd when there is something between bd

[Øystein_Kolsrud]

OK, but how would I actually do that? It doesn't seem like the index function accepts wildcards either.

[marcus_sommer]

I think you could use mapsubstring() for it. Here two examples what is meant:

Re: Passing parameter strings that contain special characters

Re: Wildcard Lookup between two tables

- Marcus

[swuehl]

You can use a macro to find and replace using regular expressions:

Regular expression [A-Z] *

How to use regular expressions

[undergrinder]

Yes, it is a bit bloat but works:

replacemap:

Mapping

load * Inline

[

ori,new

bad,b@d

bbd,b@d

bcd,b@d

bfd,b@d

bxd,b@d

](delimiter is ',');

base:

Load * inline

[

Field

abcde

abfde

abde

axcde

eafgbxxdjk

eafgbxdjk

] (delimiter is '|');

Final:

Load

Field,

    MapSubString('replacemap',Field) as replace_step,

    replace(MapSubString('replacemap',Field),'@','NewString') as Final

Resident base;

drop table base;

G.

[undergrinder]

Hi  Stefan,

It is wonderful. I didn't know about there is such opportunity to use RegExp in Sense.

G.

[swuehl]

Well, I missed the QS part of the question, the link is related to QV. Need to check for QS.

[swuehl]

Maybe have a look at

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-So...

[undergrinder]

So I know there is an opportunty to write subroutine in Sense, but you can't do the same with functions.

The editor returns with 'Unknown statement: Function'