MySQL Convering string to integer within the Query for Sphinx Filter

Question

Sphinx cannot filter on strings, on integers.

I have some fields that are simple yes and no strings I would like to filter.

Is it possible to do a MySQL query something like this?

SELECT integer(FIELD:yes,1;no,2) AS sound FROM my_table;

Something like that? So that instead of yes, it gives me 1 and instead of no it gives me 2?

Then I would have as an attribute:

 sql_attr_unit = sound

and I could filter like so:

 $c->SetFilter(sound, array(1)); 

Does this look right?

Answer 1

It appears that an If Else solution is what is needed to use from this post:

How do write IF ELSE statement in a MySQL query

 SELECT (case when (Sound = 'yes') then 1 else 0 end) AS Sound FROM my_table;

This appears to be the solution for yes and no items.