Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Øystein_Kolsrud
Employee
Employee

How to Replace Substring Based on Wildcards

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 ValueResult
abcdeaXYZe
abfdeaXYZe
abdeabde
axcdeaxcde
9 Replies
bramkn
Partner - Specialist
Partner - Specialist

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
Employee
Employee
Author

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
MVP
MVP

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

Regular expression [A-Z] *

How to use regular expressions

undergrinder
Specialist II
Specialist II

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
Specialist II
Specialist II

Hi  Stefan,

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

G.

swuehl
MVP
MVP

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

undergrinder
Specialist II
Specialist II

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'