Search code examples
sqlpostgresqlbitmask

bit varying in Postgres to be queried by sub-string pattern

The following Postgres table contains some sample content where the binary data is stored as bit varying (https://www.postgresql.org/docs/10/datatype-bit.html):

ID   |   Binary data
----------------------
1    |    01110        
2    |    0111       
3    |    011         
4    |    01        
5    |    0         
6    |    00011      
7    |    0001        
8    |    000    
9    |    00          
10   |    0  
11   |    110
12   |    11
13   |    1

Q: Is there any query (either native SQL or as Postgres function) to return all rows where the binary data field is equal to all sub-strings of the target bit array. To make it more clear lets look at the example search value 01101:

  1. 01101 -> no result
  2. 0110 -> no result
  3. 011 -> 3
  4. 01 -> 4
  5. 0 -> 5, 10

The result returned should contain the rows: 3, 4, 5 and 10.

Edit: The working query is (thanks to Laurenz Albe):

SELECT * FROM table WHERE '01101' LIKE (table.binary_data::text || '%')

Furthermore I found this discussion about Postgres bit with fixed size vs bit varying helpful: PostgreSQL Bitwise operators with bit varying "cannot AND bit strings of different sizes"

Solution

  • How about

    WHERE '01101' LIKE (col2::text || '%')