Find and replace data with specific patterns

Thu, 26 Jan 2012 08:31:25 GMT

Hi there!

I was wondering if there is a better way to replace certain text patterns with another.

The reason: there are certain identifications which are used randomly in a clients dataset, but can no longer be used to ensure anonimity.

I know there is a scramble function, but we only want to tackle those identifiers which might reveal unwanted information.

Attached a simplified inline example.

[CODE]

ReplaceMyData:

LOAD

Name as [Original name],

if(substringcount(Name,'Van')=1,

replace(Name,'Van','_REPLACED_'),

if(substringcount(Name,'VAN')=1,

replace(Name,'VAN','_REPLACED_'),

if(substringcount(Name,'VAN')=1,

replace(Name,'van','_REPLACED_'),

if(substringcount(Name,'Mario')=1,

replace(Name,'Mario','_REPLACED_'),

if(substringcount(Name,'WILDER')=1,replace(Name,'WILDER','_REPLACED_'), Name))))) as [Converted name]

FROM

[SourceTable.qvd]

(qvd);

[/CODE]

Thanks in advance for your help!

Regards,

Maarten

Re: Find and replace data with specific patterns

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 08:44:17 GMT

Hi,

You could use the MapSubString with a mapping table like in the attached application.

Hope that helps.

Miguel

Thu, 26 Jan 2012 08:51:55 GMT

Miguel,

That is exactly what I was hoping for! 🙂

Thanks a ton!

Kind regards,

Maarten