MySQL string search between commas

Question

My db is built as follows:

value1,value2,value3 | 1
value4,value5,val"u6 | 2
value 5, value 6, value 8 |3

(Two columns, one with a key separated by commas and the other just a normal var-char)

I'm looking for the most reliable way to find a query within the quotes and I'm getting kinda lost here.

I'm using the word boundaries for that:

SELECT * FROM ABC WHERE content REGEXP '[[:<:]]value 5[[:>:]]'

The problem is when I'm doing this query:

SELECT * FROM ABC WHERE content REGEXP '[[:<:]]5[[:>:]]'

It will also return the value, which is not what I'm looking for. Another problem is that the word boundaries refer to quotes as a word boundary

How can I solve this and create a simple query that will only fetch the exact full query between the quotes?

BTW I don't have an option to change the DB structure...

You need to rebuild your DB and [normalize it](http://en.wikipedia.org/wiki/Database_normalization). That'll solve your problem the PROPER way. MySQL regexes are for matching only. they do not capture. — Marc B, Dec 03 '12 at 15:03
@MarcB: "*BTW i don't have an option to change the DB structure ...*" — eggyal, Dec 03 '12 at 15:03

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

4

As @MarcB commented, you really should try to normalise your schema:

CREATE TABLE ABC_values (
  id  INT,
  content VARCHAR(10),
  FOREIGN KEY (id) REFERENCES ABC (id)
);

INSERT INTO ABC_values
  (id, content)
VALUES
  (1, 'value1'), (1, 'value2'), (1, 'value3'),
  (2, 'value4'), (2, 'value5'), (2, 'val"u6'),
  (3, 'value 5'), (3, 'value 6'), (3, 'value 8')
;

ALTER TABLE ABC DROP content;

Then, as required, you can perform a SQL join between your tables and group the results:

SELECT   id, GROUP_CONCAT(ABC_values.content) AS content
FROM     ABC LEFT JOIN ABC_values USING (id) NATURAL JOIN (
  SELECT id FROM ABC_values WHERE content = 'value 5'
) t
GROUP BY id

If it is completely impossible to change the schema, you can try FIND_IN_SET():

SELECT * FROM ABC WHERE FIND_IN_SET('value 5', content)

edited May 23 '17 at 11:59

Community

1
1

answered Dec 03 '12 at 15:04

eggyal

122,705
18
212
237

well say that i can do that , how can i do it to a large db ? will the connected records will stay the same ? – lior r Dec 03 '12 at 15:19
@liorr: I'm not sure that I understand your question... are you asking how you can populate `ABC_values` from your existing `ABC` table? If you have (or can create) a separate table containing every possible `content` value, then you could do it using a join based on `FIND_IN_SET()`. – eggyal Dec 05 '12 at 13:10

score 0 · Answer 2 · edited May 23 '17 at 12:23

Another workaround is to use LIKE with the delimiters of the items in your list:

WHERE content LIKE ',5,'

But the item you're looking for may be at the start or end of the list. So you have to modify the list on the fly to include the delimiters at the start and end.

WHERE CONCAT(',', content, ',') LIKE '%,5,%'  -> this works for me on mysql

This works, and in some sense it's no worse than any other search that you do for an item in a comma-separated list. That's because such a search is bound to do a table-scan and therefore it's very inefficient. As the data in your table grows, you'll find it can't perform well enough to be useful.

See also my answer to Is storing a delimited list in a database column really that bad?

MySQL string search between commas

2 Answers2