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

Question

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.

Does the function expect two parameters - item name and color? If you wish to get all things bearing color red then passing the color name would be sufficient. — franklwambo19, Oct 16 '22 at 11:12
Please don't ask the same question twice: https://stackoverflow.com/questions/74085912/how-to-split-a-string-and-perform-a-join-using-the-components — Georg Richter, Oct 16 '22 at 19:25
@GeorgRichter, well, the context is the same, but the question is different. This question is about the function so I can get all objects with one given color, the [other one](https://stackoverflow.com/questions/74085912/how-to-split-a-string-and-perform-a-join-using-the-components) is about obtaining all colors for an object. Also, at first I had both question in the same post, but was told that only one question was allowed. — exore, Oct 17 '22 at 04:45
@FrancisOchieng, I thought the function should have 2 params, but if it can have only one, this is good too, of course. Provided I can retrieve all things with a given color, the number of parameters is not important. — exore, Oct 17 '22 at 04:48

Georg Richter · Answer 1 · 2022-10-17T07:36:32.250

2

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.

edited Oct 17 '22 at 07:36

answered Oct 17 '22 at 07:11

Georg Richter

5,970
2
9
15

waow ! very elegant solutions. I really like the shortest one with regexp. The find_in_set one may be used when it's harder to find the correct regexp. Thanks you. – exore Oct 17 '22 at 17:43

score 1 · Accepted Answer · answered Oct 17 '22 at 06:29

1

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.

answered Oct 17 '22 at 06:29

ahmed

9,071
3
9
22

1

Ah ah. Did not realize that one can join on any expression. And I would never have thought of `CONCAT('|', T.colorsid, '|')`. thank you. – exore Oct 17 '22 at 17:36
Using `things T LEFT JOIN colors D` it is even possible to get the things that don't have any color, with `WHERE D.color is null`. – exore Oct 18 '22 at 07:37
1

Do you mean something like [this](https://dbfiddle.uk/3pOSLb5R) – ahmed Oct 18 '22 at 10:26
1

Yes, exactly like this ! Either the colorsid field is null or it references a non existing color. I just thought of the first case, but you showed the second. very nice ! – exore Oct 19 '22 at 03:24

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

2 Answers2