PostgreSQL 使用 LIKE/ILIKE 加入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3772584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL join using LIKE/ILIKE
提问by burger
I am trying to do an inexact join (I am not sure what the proper term is) where I could perform pattern matching. Basically, instead of doing a JOIN like this:
我正在尝试进行不精确连接（我不确定正确的术语是什么），我可以在其中执行模式匹配。基本上，而不是像这样执行 JOIN：
.... JOIN .... ON (t1.col = t2.col)
I would like to do do something like:
我想做类似的事情：
.... JOIN .... ON (t1.col ILIKE %(t2.col)% )
The second example is obviously not proper syntax. Is there a way to do something like that?
第二个例子显然不是正确的语法。有没有办法做这样的事情？
回答by mechanical_meat
.... JOIN .... ON t1.col ILIKE '%' || t2.col || '%'
Please note that as written, AFAIK, PostgreSQL won't be able to use any indexes to speed up the join processing.
请注意，正如所写的那样，AFAIK，PostgreSQL 将无法使用任何索引来加速连接处理。
回答by bobince
An alternative way to join on “is the value of t2.col a substring of t1.col”:
另一种加入“是 t2.col 的值是 t1.col 的子字符串”的替代方法：
... AS t1 JOIN ... AS t2 ON POSITION(t2.col IN t1.col)<>0
This still can't use indexes, but the advantage is you don't have to worry about %and _characters in t2.colwhich would otherwise start matching everything.
这仍然不能使用索引，但优点是您不必担心%和_字符t2.col会开始匹配所有内容。
If you need case-insensitive matching like ILIKEand you're not using citextyou'd need to LOWER()both columns before using POSITION().
如果您需要不区分大小写的匹配，ILIKE并且您不使用citext，LOWER()则在使用POSITION().
