How can I select row by substring respect delimeter in mysql

Question

I have a field pattern (is string).

examples:

id | pattern
 1 | car;cat;dog       -> words car cat do
 2 | apple;nocar;carno -> words apple nocar carno

So, now, I would like to select all items where pattern contains full word 'car'.

Expected result is:

id | pattern
 1 | car;cat;dog

because there is car - it should skip words like nocar and carno

Thank you for any help.

```WHERE FIND_IN_SET('car', REPLACE(`table`.`pattern`, ';', ','))```. The "words" itself must NOT contain a comma. — Akina, Nov 18 '20 at 08:20

score 0 · Answer 1 · answered Nov 18 '20 at 08:22

0

Could a simple LIKE solve it?

SELECT
    *
FROM
    TABLE
WHERE
    pattern LIKE 'car;%'
    OR pattern LIKE '%;car;%'
    OR pattern LIKE '%;car';

answered Nov 18 '20 at 08:22

itainathaniel

2

I like to just do `WHERE concat(';',pattern,';') LIKE '%;car;%'` – ysth Nov 18 '20 at 08:43

1 Answers1