Mysql – test a SET data type field

MySQL

I have a SET type field in my table like:

car_features SET('Air Conditioning'
       ,'Power Windows'
       ,'Power Steering'
       ,'Moonroof'
       ,'Disk Brakes'
       ,'Power Seats'
       ,'Leather')

I'm trying to test this field in the order that the client gives to me in a input string,like:

                     "Power Windows,Moonroof,Leather,Disk Brakes"

then I want to test the field then give the result back to client like:

               +---------------+----------+---------+-------------+
               | Power Windows | Moonroof | Leather | Disk Brakes |
               +---------------+----------+---------+-------------+
               | 1             | 0        | 1       | 1           |
               +---------------+----------+---------+-------------+

according to whether the specific option is set in this field or not.

the question is,I can easily get single result like:

      select find_in_set(a_function_fetch_option_oneByone(theInputString),car_features)>0;

but I cannot combine every result into one just like I said…

somebody help me!!!

Best Answer

This may help you get closer to a solution:

You can & and | (that's 'and' and 'or') SET values. Unfortunately you get an integer, the bits of which represent the result of the bit-wise operation.

The result of & will be non-zero if some fields match.

The result of & can be fed into BIT_COUNT() to see how many things match.

(a & b) != 0

should give you 1 for the item being in the set, zero if not.

Related Question