MySQL search from pipeline delimitated string

Question

In MYSql 5.7 (i can't use PHP or similar language or other MySQL version) i have two table:

Operators and OperatorsWork

In the first table i have the ID and the name

In the second table i have a string with pipeline separeted IDs

Here you can find a DB Fiddle for test: https://www.db-fiddle.com/f/iTYVLJQgaNEs92rMQKgybP/1

Pratically i need to take the IDOperators column and match with name column

The expected result is comma separated values in this way:

1 - Name1, Name2, Name3

2 - Name2, Name3

3 - Name1, Name3

The original IDOperators was:

1 - 1|2|3

2 - 2|3

3 - 1|3

Many thanks

Normalize the schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — sticky bit, Mar 09 '21 at 15:34

score 0 · Accepted Answer · answered Mar 09 '21 at 15:43

0

Resolved in this way

SELECT  b.id,
        GROUP_CONCAT(a.name ORDER BY a.id) Result
FROM    Operators a
        INNER JOIN OperatorsWork b
            ON FIND_IN_SET(a.id, REPLACE(b.IDOperators, '|', ',')) > 0
GROUP BY b.id

answered Mar 09 '21 at 15:43

g-pane

81
6

MySQL search from pipeline delimitated string

1 Answers1