Dynamic Regular Expressions to Split Columns by Delimiters

[mholland]

Hi,

I'm trying to split columns dynamically by different delimiters, but I'm having some issues with the RegEx element and how to pass this through the RegEx tool dynamically.

Here's a view of my data:

I'm looking to split the "expected_pattern" field by the delimter found in the "expected_delimiter" field. This can be different depending on the data source. I've created a column called "Max_Count of Delimiters" to get the total number of splits I'll need to do. I've done this so all "expected_pattern" fields are split. If there are blank fields, that's fine.

I did some searching on how to generate the dynamic RegEx for this, which is the value appearing in the "Regex" column. Here's the formula I used to create this:

REPLACE(REPLACE("^(?:([^DELIM]+)DELIM){COUNT}", "DELIM", [expected_delimiter]), "COUNT", ToString([Max_Count of Delimiters]))

How can I use this RegEx to split each row dynamically by the delimiter in the "expected_delimiter" column by the total number of splits needed from the "Max_Count of Delimiters" column?

Thanks,

Mark

[Qiu]

@mholland 
Glad to be any help.

[flying008]

Hi, @mholland  @Qiu 

In any case, we always want to get the result in the simplest way, so if we replace all the specified delimiters with a very rare character, maybe we can achieve a completely dynamic split column output.