Regex Challenge:

[CiaranA]

Hi all, 

I'm trying to generate the most efficient Regex_Replace to identify string parts of a field where the pattern goes as below:

The two patterns i'm trying to pull out and update are the below: 

AAAAaaaaaaaAa,11111111,

and

AAAAaaaa,AAA11AAA1

Can anyone help?

Thanks,

Ciaran

[DavidP]

Hi Ciaran,

I'm not quite clear what you're trying to achieve. Could you provide an example?

[CiaranA]

So i have a long string field with say 250 characters in it, but this length is variable. 

I need to identify two sections in the string which follow the below structure, and replace with a new string from another field. 

to mask the data I'll use A to represent any upper case letter, a to represent any lower case, , = commas, and 1 equals any number 1-9

Section 1 which has the following structure - AAAAaaaaaaaAa,11111111,

Section 2 which has the following structure - AAAAaaaa,AAA11AAA1,

[Jonathan-Sherman · Accepted solution]

Hi @CiaranA,

I think this might be what you're trying to achieve?

Before:

After:

I've used two Regex_Replace formulas:

REGEX_Replace([Text], '[A-Z]{4}[a-z]{7}[A-Z][a-z],\d{8},', [TextToAdd1])

REGEX_Replace([Text], '[A-Z]{4}[a-z]{4},[A-Z]{3}\d{2}[A-Z]{3}\d,', [TextToAdd2])

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[DavidP · Accepted solution]

Try this:

First pattern:   \u{4}\l{7}\u\l\,[1-9]{8}\,

Second pattern: \u{4}\l{4}\,\u{3}[1-9]{2}\u{3}[1-9]\,

If the numbers were 0-9 you could simply use \d instead of [1-9]

[CiaranA]

Thanks @DavidP & @Jonathan-Sherman for your responses, that helped us get to our final solution which works 🙂

1. REGEX_Replace([FIELD1], "STRING1,[0-9]*,", "STRING1,"+[FIELD2]+",")

2. REGEX_Replace([FIELD1], "STRING2,[0-9a-zA-Z]*,", "STRING2,"+[FIELD3]+",")