CODEWITHSUNDEEP
sqlms-access
user2096512
user2096512

Reputation: 469

Join tables on fields that don't match?

I'm trying to join 2 tables but the contents of the fields I'm trying to join on are in different formats. 

Table1 name field is just is normal - Dave, Bob, Gary
Table2 name field some of them have brackets after - Dave (GB), Bob, Gary (JAP)

In vb.net I would just split the name by " (", is there a similar thing I can do in Access in the JOIN query?

I.E

INNER JOIN People ON (SPLIT(Moves.name, " (", "") = People.name);

Upvotes: 1

Views: 542

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

You could join on the result of InStr:

select *
from table1 t1
inner join table2 t2 on InStr(t2.name, t1.name & ' (')=1

t1 has "clean" strings; t2 has strings with (...) suffixes. The above join expression appends space+open parenthesis string ' (' to the field from t1, and checks that the field from t2 starts with that string.

Upvotes: 1

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4802

String REPLACE and SUBSTRING approach could be viable

    SELECT CASE WHEN 'Dave' = REPLACE('Dave (GB)',SUBSTRING('Dave (GB)', CHARINDEX(' ', 'Dave (GB)'), Len('Dave (GB)')), '')  THEN 'Yes'
    ELSE 'NO' END

Replace 'Dave' and 'Dave (GB)' with the column names in your respective tables use the below condition in your join.

    'Dave' = REPLACE('Dave (GB)',SUBSTRING('Dave (GB)', CHARINDEX(' ', 'Dave (GB)'), Len('Dave (GB)')), '')

Upvotes: 0

Related Questions