-1

There is a table with a tag column that has different tags separated by a , like below:

| id | tag   |
| -- | ----- |
| 1  | a,b,c |
| 2  | b,a,h |
| 3  | c,g,e |

How can I select every rows that has for example a and b in their tag column.

I am using REGEX in MySQL, the problem is when the position of tags changes (ie: a,b and b,a), I cannot get all rows with the desired tags.

Here is the query to get both a and b tags:

SELECT * FROM sample_table WHERE tag REGEXP 'b.*a'

I am seeking for a regex that is indifferent to the position of the tags.

SEYED BABAK ASHRAFI
  • 4,093
  • 4
  • 22
  • 32

2 Answers2

1

I think that using a couple of find_in_set calls would be easier than a regex:

SELECT *
FROM   sample_table
WHERE  FIND_IN_SET('a', tag) > 0 AND
       FIND_IN_SET('b', tag) > 0
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Unfortunately, It doesn't help me sir. Searching for `a` and `b` is just an example, I have to do a filter keys on different categories based on this *tag* and using regex is mandatory for me. – SEYED BABAK ASHRAFI Aug 09 '20 at 08:37
0

And please read also Is storing a delimited list in a database column really that bad?

CREATE TABLE table1 (
  `id` INTEGER,
  `tag` VARCHAR(5)
);

INSERT INTO table1
  (`id`, `tag`)
VALUES
  ('1', 'a,b,c'),
  ('2', 'b,a,h'),
  ('3', 'c,g,e');

SELECT * FROm table1 WHERE tag REGEXP 'a+.*,b+' OR tag REGEXP 'b+.*,a+'

id | tag  
-: | :----
 1 | a,b,c
 2 | b,a,h

SELECT * FROm table1 WHERE tag REGEXP '(a+.*,b+|b+.*,a+)'

id | tag  
-: | :----
 1 | a,b,c
 2 | b,a,h

db<>fiddle here

To address you other, you make a string, with all posible poattern [a,b,b,c,d,e,f] Give it to a stored procedure, Split that thingl and than makle a huge where clause

This is basically text CONCAT("tag REGEXP '", pattern1,"+.*,",pattern2,"+' OR tag REGEXP '",pattern2,"+.*,",pqattern1,"'")

and so you replace a nd b with an element fro the string.

That will take some programming, but better that writing so may patter by hand

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Considering this solution, I have to make different possibilities of `ab`. For example if I have `a,b,c,d,e,f`. I guess I have to make `2^6` different possibilities to get the desired result. Am I right? – SEYED BABAK ASHRAFI Aug 09 '20 at 08:39
  • it is the question, if you can find a pattern, for your wanted search, like i did. basically more complicate it gets more you have to know regex, so look here to normalize the table and do a "normal" sql search, that will be complicated as well, but regex is somewhat strange to handle – nbk Aug 09 '20 at 08:43
  • I know that the structure of the table is a nightmare, but I am editing an old project which is online I have to make this filter on it. I appreciate it if you help me on the regex – SEYED BABAK ASHRAFI Aug 09 '20 at 08:45
  • This is matching `aaa, aaa`, no `b` here! – Toto Aug 09 '20 at 08:52