How many values of an array in a MySQL string

Question

I have a simple array with some values:

$firstArray = array (
  ['val1'] => 'a',
  ['val2'] => 'b',
  ['val3'] => 'c'
);

and I would like to find how many values of this array appear in every line of a MySQL table like this one:

| ID |  Name  |    Val    |
| 01 | voice1 | a,c,d,e   |    // it should return 2 (a and c)
| 02 | voice2 | a,b,c,d,f |    // it should return 3 (a,b and c)

The best thing should be to have an array with ID, name, and how many values.

Orden · Accepted Answer · 2016-01-13 18:06:49Z

2

You can use the FIND_IN_SET that will return you the position of the matched value and convert it to 1 or 0 with the SIGN function.

select
    SIGN(FIND_IN_SET('a', texte)) +
    SIGN(FIND_IN_SET('b', texte)) +
    SIGN(FIND_IN_SET('c', texte))
from your_table;

This returns exactly what you expected with your test data-set.

Regards,

answered Jan 13, 2016 at 18:06

Orden

6414 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DavyJones Over a year ago

Thank you and sorry for the delay!

DavyJones Over a year ago

Sorry... a part of the question was "The best thing should be to have an array with ID, name, and how many values"... how to obtain it?

Orden Over a year ago

You just have to add: "ID, name, " just after the select.

Collectives™ on Stack Overflow

How many values of an array in a MySQL string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related