Find MySQL DB rows with a match in a pipe delimited column

Question

I'm querying a table that has a column with member_ids stuffed in a pipe delimited string. I need to return all rows where there is an 'exact' match for a specific member_id. How do I deal with other IDs in the string which might match 'part' of my ID?

I might have some rows as follows:

1|34|11|23
1011
23|1
5|1|36
64|23

If I want to return all rows with the member_id '1' (row 1, 3 and 4) is that possible without having to extract all rows and explode the column to check if any of the items in the resulting array match.

You could `REPLACE()` the `|` with commas and feed that to [`FIND_IN_SET()`](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set). It'll be slow, for certain. If you have the ability to refactor this and properly normalize it into a one-to-many table relation, that is a _much_ better long term plan. — Michael Berkowski, Jul 07 '14 at 23:37
In fact, here's exactly that implementation: http://stackoverflow.com/questions/18850372/mysql-pdo-query-like-with-pipes — Michael Berkowski, Jul 07 '14 at 23:39
@MichaelBerkowski thanks for the suggestion. This is data being stored by a third party add-on in a CMS. Rather than try to work with that it might be wiser for me to simply code my own solution with a more sensible underlying storage approach. :) — Nathan Pitman, Jul 08 '14 at 00:36

score 4 · Accepted Answer · edited May 23 '17 at 11:45

MySQL's regular expressions support a metacharacter class that matches word boundaries:

SELECT ...
FROM mytable
WHERE member_ids REGEXP '[[:<:]]1[[:>:]]'

See http://dev.mysql.com/doc/refman/5.6/en/regexp.html

If you don't like that, you can search using a simpler regular expression, but you have to escape the pipes because they have special meaning to regular expressions. And you also have to escape the escaping backslashes so you get literal backslashes through to the regular expression parser.

SELECT ...
FROM mytable
WHERE member_ids REGEXP '\\|1\\|'

You can do this in one expression if you modify your strings to include a delimiter at the start and the end of the string. Then you don't have to add special cases for beginning of string and end of string.

Note this is bound to do a table-scan. There's no way to index a regular expression match in MySQL. I agree with @MichaelBerkowski, you would be better off storing the member id's in a subordinate table, one id per row. Then you could search and sort and all sorts of other things that the pipe-delimited string makes awkward, inefficient, or impossible. See also my answer to Is storing a delimited list in a database column really that bad?

Thanks for your response Bill. Really helpful. :) – Nathan Pitman Jul 08 '14 at 00:38 — Nathan Pitman, Jul 08 '14 at 00:38

Find MySQL DB rows with a match in a pipe delimited column

1 Answers1