Joining 2 sql tables based on substring

问题:

I am trying to join tables where the only 2 keys which match are table1.Account and table2.key

But the problem is the setup. The table1.Account field has 10 digits and only the middle 4 or 5 digits match with the table2.key

eg : 1234xxxx10 - > table1.Account matches with xxxx -> table2.key

or   123xxxxx10 - > table1.Account matches with xxxxx -> table2.key

I've written this piece of INNER JOIN code but the query keeps on running and is not giving me back anything.

SELECT DISTINCT column1, column2
from table1 INNER JOIN table2 ON table1.Account like '%'+table2.key+'%'
order by column1

回答1:

SQL has a substring function:

https://msdn.microsoft.com/en-us/library/ms187748.aspx

SELECT DISTINCT column1, column2
from table1 
INNER JOIN table2 
    ON table1.Account = substring(table2.key, 3, 4)
    OR table1.Account = substring(table2.key, 4, 4)
order by column1

I don't know if you need the 'or' in your on clause, but based on your question it seems like there may be two ways for those fields to match up. Regardless, you can modify the on clause, as needed, but this sample should help you with the syntax, which appears to be your obstacle.