How to write a function to split a string and replace the elements by values from another table?-CodePudding

Mariadb version 10.3.34. SQL to create the example tables is on this gist.

I have to work with a foreign database on which I have no control. So suggestions to modify the structure of the DB are, sadly, unacceptable. I can add functions, though.

Now, in this database, things can have from 0 to n colors, and the color references are coded as a string of all possible values joined by a | char. I know this is a bad practice, but this is not my db, I can't change it.

 ---------------------- 
|          things      |
| name (pkey)| colorsid|
 ------------ --------- 
| 'door'     | '20|5'  |
| 'car'      | '10'    |
| 'hammer'   | null    |
| 'box'      | '5'     |
 ------------ --------- 

 ------------------ 
|    colors        |
| id   | color     |
 ------ ––––––––––– 
| 5    | 'red'     |
| 10   | 'blue'    |
| 20   | 'black'   |
 ------ –––––––––––

So the door is black and red, the car is blue, the hammer has no color, and the box is red.

Is there a way to build a thing_has_color function so I could do something like this:

SELECT name from things WHERE thing_has_color( name, 'red' );

The result would be

 -------- 
| name   |
 -------- 
| 'door' |
| 'box'  |
 --------

Performance is not an issue (to a reasonable extent, of course). The DB is expected to contain at most a few tens of colors, and no more than 10 000 things.

CodePudding user response：

You may join the tables as the following:

SELECT T.name 
FROM things T JOIN colors D
ON CONCAT('|', T.colorsid, '|') LIKE CONCAT('%|', D.id, '|%')
WHERE D.color = 'red'

See a demo.

CodePudding user response：

MariaDB has a FIND_IN_SET function, where set is a list of comma separated values. Just replace pipe by comma:

SELECT name FROM things 
WHERE FIND_IN_SET((
   SELECT id FROM colors WHERE color="red"),
   REPLACE(colorsid,"|", ","));

Another option would be to use a regular expression:

SELECT name FROM things 
WHERE colorsid REGEXP 
  concat("[[:<:]]",(SELECT ID FROM colors WHERE color="red"),"[[:>:]]");

However both solutions will be slow, since they can't use an index.