Problem

You have set up some bitmask values and you would like to return the calculated value given a string with one or more of the codes used, to be able to used it in an SQL query.

Solution

You can use the following to create a function called bitmask. This one deals with any number of individual codes (2 and 3 digit codes in this case).
It also does not calculate duplicate values since they are removed.
So for this example the string ’02a’ is the same as ’02a,02a’ and should give 16 in both cases.

CREATE OR REPLACE FUNCTION bitmask(code text) RETURNS integer AS $$
  DECLARE bm INTEGER := 0;
  DECLARE total INTEGER := 0;
  DECLARE counter INTEGER := 0;
  DECLARE code_to_translate TEXT := '';

  BEGIN
    LOOP 
      EXIT WHEN LENGTH(code) = 0;
      /* only take first 2 or 3 characters */
      code_to_translate := SPLIT_PART(code, ',', 1);
      CASE code_to_translate
        WHEN 'XX'  THEN bm =      0;
        WHEN '01a' THEN bm =      2;
        WHEN '01b' THEN bm =      4;
        WHEN '01c' THEN bm =      8;
        WHEN '02a' THEN bm =     16;
        WHEN '02b' THEN bm =     32;
        WHEN '03'  THEN bm =     64;
        WHEN '04a' THEN bm =    128;
        WHEN '04b' THEN bm =    256;
        WHEN '04c' THEN bm =    512;
        WHEN '04d' THEN bm =   1024;
        WHEN '05'  THEN bm =   2048;
        WHEN '06a' THEN bm =   4096;
        WHEN '06b' THEN bm =   8192;
        WHEN '10a' THEN bm =  16384;
        WHEN '10b' THEN bm =  32768;
        WHEN '10c' THEN bm =  65536;
        WHEN '10d' THEN bm = 131072;
        WHEN '11a' THEN bm = 262144;
        WHEN '11b' THEN bm = 524288;
        ELSE bm = 0;
      END CASE;
      total := total + bm;
      counter := counter + 1;
      /* Remove checked code so we do not have duplicates */
      code := regexp_replace(code, code_to_translate||'[,]*', '', 'g');
    END LOOP ;
   RETURN total;
  END;
$$ LANGUAGE plpgsql;