MySQL string to set and get intersection

Question

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?

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".

Thank you.

Answer 1

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

See http://sqlfiddle.com/#!2/7b86f/3

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...

Answer 2

The code from Steve does not work in all cases. For e.x it does not work when a number can be found in another number. INSERT INTO tbl (col1, col2) VALUES ('60,61,64,68,73', '14,16,17,18,1'); With a little tweak it can work:

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