0

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!

Robbie
  • 121
  • 11
  • Please read the [question guide](https://stackoverflow.com/help/how-to-ask) and how to provide a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Stu Feb 06 '22 at 15:24
  • Would there always be a space between the primary part and a suffix? – June7 Feb 06 '22 at 17:09
  • Hi @June7, Mainly yes. you can see "att/m T1", "att/m T3", "att/m T5", "att/m Temp" – Robbie Feb 06 '22 at 18:39

1 Answers1

1

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

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

Or

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.

June7
  • 19,874
  • 8
  • 24
  • 34