Fetching a MySQL SET column as a list of integer offsets

Question

I have a table in a MySQL database defined similar to the following:

CREATE TABLE doc_types (
    id    INT UNSIGNED PRIMARY KEY,
    types SET('foo','bar','baz','quux',...) NOT NULL
)

I would like to get the value of the types column as a comma-separated list of 1-based integer offsets into that list of set values, e.g. "1,3,4" for a row whose value is "foo,baz,quux". (This format is required by another tool in the system I'm working on, and for various practical reasons I'm not able to modify it to accept something different.) The built-in EXPORT_SET function is not entirely dissimilar, but its return value would be in the form "1,0,1,1,...", essentially a 64-element bit set represented in string form.

Is there any good way to SELECT from doc_types and get the types values back in the offset format I described above, within a single SQL query?

Answer 1

Try this:

SELECT CONCAT_WS(',', 
  IF(types & 1 > 0, 1, NULL), 
  IF(types & 2 > 0, 2, NULL), 
  IF(types & 4 > 0, 3, NULL), 
  /* fill in rest here */
  ) as output 
FROM doc_types;