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

Question

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

Navigation

#1 by (2 votes)

2

Good afternoon!

I'm not an expert on mysql - so I doubt it - it 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 I have:

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

What do I need to do:

A Ranking showing which colors are preferred by customers.

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

Then 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 - it would give me a hard time - so I doubt if it's possible to do this via MySql, to see and it's simpler.

Thank you!

mysql

asked by anonymous 18.09.2018 / 22:40

1 answer

How to copy an html block when clicking a button on the Angular? JQuey-ui depends on JQuery

score 2 · Answer 1

This gave a bit of work, but come on ...

Solution center is in function FIND_IN_SET of mysql , which looks up the index of a value in a comma-separated list. Basically it does 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

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

To solve only with SELECT , I created a table with the colors, so it is possible to make a select in it and group later by color, adding the occurrences.

If you did not use this, you would have to do a CTE ( Common Tabel Expression to resolve and maybe one more query, so I think it is 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 in SQL Fiddle

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