JOIN语句的另一个子字符串中包含的字段的SQL子字符串

Question

我使用来自NYC MTA turnstile data和subway station location的两个数据集，一个包含在特定地铁站收集的转门数据，另一个包含所述地铁站的经度和纬度。表之间没有公共键。我曾希望使用地铁站名称，但是在一个表中有许多不同的站包含相同的名称，此外，表之间的命名约定略有不同。为了克服这个问题，我想使用地铁站名称和地铁站中的线路的组合来连接基于子字符串的表。

例如：

在火车站位置表中，有一行包含

+------------------------+-----------------+
|Name                    |Line             |
+------------------------+-----------------+
|Lexington Ave - 59th St | 4-5-6-6 Express |
+------------------------+-----------------+

而在火车站数据表中，一行可能如下所示

+---------+-----------------+
| Station | LineName        |
+---------+-----------------+
| 59 ST   | NQR456W         |
+---------+-----------------+

我能想到的最好的解决办法是使用LIKE关键字或LOCATE函数执行某种类型的搜索，返回包含相同的车站和线路字符子字符串的单数行，即LIKE("%59%") AND NQR456。我希望忽略像ST和AVE这样的子字符串和像‘’-‘’这样的字符。

一旦我有了这些行，我想为我可以在其上执行JOIN语句的每个工作站创建一个新列，该列具有共享的唯一id的适当键。

预先感谢您的所有帮助

我尝试了下面的查询，但由于仅在另一个子字符串中搜索一个子字符串，因此无法正常工作

SELECT tsl.station, td.station, td.linename, tsl.line
FROM train_station_locations tsl, turnstile_data td
WHERE CONCAT('%',LOWER(tsl.station),'%')
 LIKE CONCAT('%', REPLACE(REPLACE(td.station," st","")," ",""),'%') 
 AND  CONCAT('%',LOWER(td.linename),'%') LIKE 
 REPLACE(CONCAT('%',LOWER(tsl.line),'%'),"-","");

我已经提到了以下问题

https://stackoverflow.com/a/40140482/9367155

SQL: Join tables on substrings

Answer 1

处理没有PK的数据一定很令人沮丧……

根据上面分享的数据，似乎可以从两个字段中剔除非数字字符并查找匹配项。59 = 59。

MySQL 8支持REGEXP_REPLACE：https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

在MySQL 8之前，您可以创建自定义函数：MySQL strip non-numeric characters to compare