SQL Query在前缀和非前缀字段上连接两个表(SQL Query that joins two tables on a prefixed and non prefixed field)

 我的Access数据库中有两个表a和b 。 在两个表中我都有字段ID。 但是在表格中，我的ID字段以“31”为前缀，而表b中的 ID字段则不是。 
 
 所以举个例子 
 
table a                 table b

ID      field2          ID     field3
31L123  test123         L123   123test
31L122  test321         L122   321test
 
 我的前缀字段表是从Excel导出定期导入的，我知道我可以删除excel级别的前缀但是有没有办法通过在SQL中的连接上使用某种Concatenate函数来连接ID字段上的两个表什么机会声明？ 
 
 例如，类似的东西： 
 
SELECT Id, Field2, Field3 
FROM a LEFT JOIN b ON CONCATENATE('31', a.ID) = b.ID
WHERE a.Field2 = 13
 
 我不确定这是否是正确的方法 - 这就是为什么我似乎无法找到任何关于我的问题的现有帮助（忽略在导入之前处理excel级别的字段）。 

I have two tables a and b in my Access Database. In both tables I have the field ID. However in table a my ID field is prefixed with '31' where as my ID field in table b is not. 
 
So for example
 
table a                 table b

ID      field2          ID     field3
31L123  test123         L123   123test
31L122  test321         L122   321test
 
My prefixed field table is imported regularly from an Excel export, I understand I could remove the prefix at the excel level but is there a way to join the two tables on the ID field by using some sort of Concatenate function on the join within the SQL statement by any chance?
 
So for example something along the lines of:
 
SELECT Id, Field2, Field3 
FROM a LEFT JOIN b ON CONCATENATE('31', a.ID) = b.ID
WHERE a.Field2 = 13
 
I am not sure if this is the correct approach or not - and that is why I can not seem to find any existing help on my problem (ignoring processing the fields at the excel level before the import).

原文：https://stackoverflow.com/questions/36063830