CODEWITHSUNDEEP
mysqlsql
lior r
lior r

Reputation: 2290

MySQL string search between commas

My db is built as follows:

  1. value1,value2,value3 | 1
  2. value4,value5,val"u6 | 2
  3. 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...

Upvotes: 1

Views: 1914

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

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?

Upvotes: 0

eggyal
eggyal

Reputation: 125835

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)

Upvotes: 4

Related Questions