MySQL的字符串来设置和获取交集 [英] MySQL string to set and get intersection

问题描述

我有一个在文本列如1,2,5,10一个ID套一个坏投影数据库。我需要这是由同样的方法设置两列的交叉点。
我不喜欢使用PHP或其他脚本语言做到这一点，我也不会像MySQL的自定义函数。
有没有什么办法让通过逗号分隔符的字符串给出两个集合的交集？

I have a bad-projected database which have a ID sets in text columns like "1,2,5,10". I need to get an intersection of two columns which are set by same way. I don't like to do it using PHP or another scripting language, I also not like MySQL custom functions. Is there any way to get an intersection of two sets given by comma-delimeter strings?

其实我并不需要有充分的十字路口，我只需要知道的是有两套相同的编号。如果是的话，我需要有1，如果没有相同的数字，我需要有0。

Actually I don't need to have full intersection, I just need to know is there same numbers in two sets. If yes, I need to have "1", if no same number, I need to have "0".

感谢您。

推荐答案

您可能能够使用 REGEXP 有位聪明的更换来做到这一点。

You may be able to use REGEXP to do this with a bit of clever replacing.

想这应该这样做（免责声明：没有测试它广泛地）：

Think this should do it (disclaimer: haven't tested it extensively):

SELECT col1,
       col2,
       CONCAT('(', REPLACE(col2, ',', '(\\,|$)|'), '(\\,|$))') AS regex,
       col1 REGEXP CONCAT('(', REPLACE(col2, ',', '(\\,|$)|'), '(\\,|$))') AS intersect
FROM tbl

请参阅 http://sqlfiddle.com/#!2/7b86f/3

要解释一下：这皈依 COL2 成常规的前pression反对 COL1 匹配。在（\\ | $）位对逗号或字符串的结尾匹配。希望这有助于...

To explain: This converts col2 into a regular expression for matching against col1. The (\,|$) bit matches against either a comma or the end of the string. Hope this helps...

这篇关于MySQL的字符串来设置和获取交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！