Match One Value between Two Strings

[Bonediggler]

Experts - 

Here's the case: I need to figure out if there is at least one common value present in 2 different strings.  For example:

String A: 123, abd, edf

String B: 345,edf,789

In this case there is a match on "edf". 

I am looking for a solution that:

> Doesn't involve parsing and then transposing the strings as that will create millions of records

> Leaves the strings as they are

> Doesn't involve lots of tools - as I already have a solution that involves lots of tools

If I am overlooking an existing function or simple process to get this done, please let me know!

Thanks in advance!

[atcodedog05 · Accepted solution]

Hi @Bonediggler 

I didnt know that these were 2 columns. If so here is a modification of @Maskell_Rascal to optimize it further.

You can split only one column and use contains to check if that keyword exists in other. This drastically reduced the number of row.

Hope this helps : )

[Maskell_Rascal]

Hope it works, and good luck! 😁

[atcodedog05]

@Maskell_Rascal your GIF is not convincing to the Good luck though 😅

[Bonediggler]

Nice improvement on the prior solution!  I would note however that using a multi-field tool to get rid of white space is generally much more performant than the data cleansing tool.  

Either way, thanks!

[atcodedog05]

@Bonediggler 

True that use Mult-field tool improves performance over using data cleanse tool.

[Maskell_Rascal]

I avoid the Data Cleansing tool at all costs. It is the single greatest performance killer in any of my workflows. 

[john_watkins · Accepted solution]

Here is an example that also does no parsing using REGEX.  I need to force myself to get better with this tool, but this works as well and generates no rows.

[atcodedog05]

Nicely done @john_watkins 🙂 👍

Checking for duplicate words. Why didn't I think about that😅

[Bonediggler]

Well done!  This definitely gets us around the exploding rows problem.  Unfortunately using the regex logic is significantly less performant, at least with the data set I am using.  Can't have your cake and eat it too I suppose....

[Maskell_Rascal]

@john_watkins - I had to come back to this post again just to say that this is an awesome solution! I will definitely be bookmarking this for reference later. 🙌😀