How to join strings when they are not 100% equal?-CodePudding

I have 2 tables (From 2 tools agents use) and I need to join them a different levels: country, etc...

One of levels I need to join them is a code that indicate your role in the company. Problem is that this code is entered in 2 different ways in in both tools but they are the same meaning.

For example, in one tool is entered as "att/m T1" and in the other tool is entered as 'att/m" so a join would not join these 2. It is not only one case, but I have different scenarios where codes are entered somehow differently.

I need to join at this field. How could I solve this issue? I tried to create Case When, but it is not scalable. Any idea how I could tackle this?

Thanks!

CodePudding user response：

I can build and test only with Access database SQL. Consider:

SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE (((Table1.T1) Like [Table2].[T2] & "*"));

Extract the key value from first table string.

SELECT IIf(Instr(T1," ")>0,Left([T1],InStr([T1]," ")-1), T1) AS D, F1 FROM Table1;

Join that query to second table.

SELECT Table2.*, Query1.* FROM Query1 INNER JOIN Table2 ON Query1.D = Table2.T2;

Consistency of structure is critical when parsing strings. Any variation will complicate the extraction.

I don't know if one approach will be faster than other with large dataset.