Is it possible to count word order in a field via Mysql?

Question

good afternoon!

I’m not an expert in mysql - so doubt - q might even be silly.

I have a field in a database where a color preference list is stored.

Ex:

Joao prefere: Azul, Verde, Vermelho.
Maria prefere: Verde, Azul, Vermelho
Pedro prefere: Azul, Verde, Vermelho.

in the database have:

Usuario | Cores
Joao    | Azul, Verde, Vermelho
Maria   | Verde, Azul, Vermelho
Pedro   | Azul, Verde, Vermelho

What I need to do:

A Ranking showing which customers' preferred colors.

In this case each color would be scored according to your order.

So in our example:

AZUL: teria 4 pontos (2x 1o lugar + 1 x 2o lugar)
VERDE: teria 5 pontos (2x 2o lugar + 1x 1o lugar)
VERMELHO: teria 9 pontos (3x 3o lugar)

The color with LESS POINTS would be preferred.

Is there any way to do this ranking using only Mysql commands?

I can do this via ASP - would be a job - so the doubt is possible to do this via Mysql, to see and is simpler.

thank you!

Answer 1

That took a little work, but come on...

The center of the solution is in the function FIND_IN_SET of mysql, looking for the index of a value in a comma-separated list. Basically it makes a split and returns the position of a value in a list, exactly what we need. Example:

SELECT FIND_IN_SET('Azul', 'Verde,Azul,Vermelho');

Returns 2, and so on

One detail is that the list can not have spaces, otherwise the word is not found ('Green, Blue, Red', would be ' Blue' and not 'Blue'), so I also used the function REPLACE to remove the spaces.

To settle with SELECT, I created a table with colors, so it is possible to select it and group it by color, adding the occurrences.

If I didn’t use that, I’d have to do a CTE(Common Tabel Expression) to solve and perhaps one more query, so I find it acceptable to create a table with the names of the colors.

The result was:

select c.cor, SUM( FIND_IN_SET(c.cor, REPLACE(t.cores, ' ', '' )) )as TOT
 from cor c, teste t
group by c.cor
order by TOT;

You can see it working on SQL Fiddle

Test table with data from your example, color table only with colors.