根据子字符串连接2个SQL表 [英] Joining 2 sql tables based on substring

问题描述

我正在尝试联接只有两个键为table1.Account和table2.key

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

但是问题是安装程序. table1.Account字段有10位数字，只有中间的4或5位数字与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

我已经编写了这段INNER JOIN代码，但是查询一直在运行，并且没有给我任何回报.

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

推荐答案

SQL具有子字符串功能:

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

我不知道您的on子句中是否需要'or'，但是基于您的问题，这些字段似乎可能有两种匹配的方式.无论如何，您都可以根据需要修改on子句，但是此示例将帮助您使用语法，这似乎是您的障碍.

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.

这篇关于根据子字符串连接2个SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！