Qfyilyi

Lets have table with 3 columns: key, value, and bitmask (as varchar; of unknown maximum length):

abc | 23 | 101

xyz | 56 | 000101

Is it possible to write query, where on the output I will get one row for every combination of key, value, and 1 in bitmask, with index of that 1 as integer column (doesnt matter if starting from 0 or 1)? So for example above:

abc | 23 | 1

abc | 23 | 3

xyz | 56 | 4

xyz | 56 | 6

Thanks for any ideas!

I think you might be better off choosing a maximum length for your varchar.

SELECT * FROM

table 

INNER JOIN

generate_series(1,1000) s(n) 

ON 

  s.n <= char_length(bitmask) and 

  substring(bitmask from s.n for 1) = '1'

We generate a list of numbers:

s.n

---

1

2

3

4

...

And join it to the table in a way that causes repeated table rows:

s.n bitmask

--- ------- 

1   000101

2   000101

3   000101

4   000101

5   000101

6   000101

1   101

2   101

3   101

Then use the s.n to substring the bitmask, and look for being equal to 1:

s.n bitmask substr

--- ------- ------

1   000101  --substring('000101' from 1 for 1) = '1'? no

2   000101  --substring('000101' from 2 for 1) = '1'? no

3   000101  --substring('000101' from 3 for 1) = '1'? no

4   000101  --substring('000101' from 4 for 1) = '1'? yes...

5   000101

6   000101

1   101

2   101

3   101

So the s.n gives us the number in the last column of your desired output, and the where filters to only rows where the string substring works out