[Join dataset with Regex]

phongtruong

Hello guys, hope you have a nice day! I am joining 2 datasets with slightly different keys. One has the structure of [random characters] + [value] + [random characters], and one is value. Could you guys suggest on how to join these 2 dataset? I'm thinking using MySQL join, combined with some regex. However I could not find a proper syntax for this. I'm also not sure if this method is possible.

 

Thank you for your inputs!

AS

Regex could work as long as there's a pattern with the random character prefix and suffix.

Is the prefix always only 3 characters, for example?

If that's the case you could just as easily use some substring funtions to extract the value.

https://www.w3schools.com/sql/func_mysql_substring.asp

 

 

Another way to join would be to use a LIKE comparison like this:

WHERE a.value LIKE CONCAT('%',b.value,'%')

 

 

That would also work as long as value isn't part of the random characters and still maintains its uniqueness in the across all the strings.

AS

Regex could work as long as there's a pattern with the random character prefix and suffix.

Is the prefix always only 3 characters, for example?

If that's the case you could just as easily use some substring funtions to extract the value.

https://www.w3schools.com/sql/func_mysql_substring.asp

 

 

Another way to join would be to use a LIKE comparison like this:

WHERE a.value LIKE CONCAT('%',b.value,'%')

 

 

That would also work as long as value isn't part of the random characters and still maintains its uniqueness in the across all the strings.