You are right in thinking that items of a list should be stored as individual values of a column, i.e. in separate rows of that column, rather than as a single value (CSV string or anything like that) – at least if you expect to have queries against individual items of the list. Relational databases are designed to work that way, and storing multiple values of a column as a list (single value) is a no-no in such cases.
Regarding your data sample of fruits and vegetables, I am assuming that you are not thinking of splitting two lists of values exactly like that, storing each list's elements alongside each other on the same row without there actually being a relationship between them, just for the sake of normalisation of the way the vegetables and fruits are stored. That would be wrong.
I mean, if currently you have a row with a list of fruits and a list of vegetables such that each fruit and each vegetable are related to this particular row, i.e. like this:
FoodsID Fruits Vegetables other columns
------- -------------------------- ------------- ----- -------
100 Apple,Avocado,Mango,Orange Corn,Tomatoes ... ...
and you want to expand each list into a row set while keeping the relationship of each fruit and each vegetable to the Foods
row, you do not just add more rows and put the first item of one list together with the first item of the other list on the same row, then the second item of each list on another row and so on, like this:
FoodsID Fruits Vegetables ...
------- ------- ---------- -----
100 Apple Corn ...
100 Avocado Tomatoes ...
100 Mango ...
100 Orange ...
Again, that would be wrong. Fruit Apple
may well be related to food item 100 as may vegetable Corn
, but if these two items are not related to each other, there is no reason to store them on the same row.
Commonly, when you want to store a relationship between a subset and an item, you use a separate table. As you have two subsets of different kinds that are related to the same item, you just use two tables. So it would be:
table Foods
:
FoodsID other columns
------- ----- -------
100 ... ...
table Fruits
:
FoodsID Fruit
------- -------
100 Apple
100 Avocado
100 Mango
100 Orange
table Vegetables
:
FoodsID Vegetable
------- ---------
100 Corn
100 Tomatoes
where FoodsID
in table Foods
would be the primary key of that table and same-named column in tables Fruits
and Vegetable
would serve as a reference (foreign key) to the corresponding row in Foods
.
This way, when querying against individual items of either list as belonging to that particular row in Foods
, you would probably use a join. Your typical filter conditions would be as simple as Vegetable = 'some vegetable'
or Fruit IN ('some fruit', 'some other fruit')
– not FindPos(Vegetable, 'some vegetable') > 0
or anything of the kind. The conditions would be simpler because each column would now contain one value per row. That will lend well to further optimisation of performance, because when you query often against a column value (without applying a function to it first, that is), you can make such queries faster by adding an index on the column – something that would be pointless for lists stored as a single value.
More can be said on this topic, which would, however, be beyond the scope of your question. I suggest you look up the terms I highlighted in bold for more information.
Best Answer
You have to use the md5 hash as a part of the string so you have to concat it
db<>fiddle here
still md5 is old and not very secure, so you should use a more secure version