bit.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

SELECT POSITION(B'' IN B'');  -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'101101' IN B'001011011011011000');  -- 3
 position 
----------
        3
(1 row)

SELECT POSITION(B'10110110' IN B'001011011011010');  -- 3
 position 
----------
        3
(1 row)

SELECT POSITION(B'1011011011011' IN B'001011011011011');  -- 3
 position 
----------
        3
(1 row)

SELECT POSITION(B'1011011011011' IN B'00001011011011011');  -- 5
 position 
----------
        5
(1 row)

SELECT POSITION(B'11101011' IN B'11101011'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'11101011' IN B'011101011'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'11101011' IN B'00011101011'); -- 4
 position 
----------
        4
(1 row)

SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'111010110' IN B'111010110'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
 position 
----------
        4
(1 row)

SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'111010110' IN B'11101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'011101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'00011101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'111010110'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
 position 
----------
        4
(1 row)

SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14
 position 
----------
       14
(1 row)

SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15
 position 
----------
       15
(1 row)

SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17
 position 
----------
       17
(1 row)

SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19
 position 
----------
       19
(1 row)

SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0
 position 
----------
        0
(1 row)

-- Shifting
CREATE TABLE BIT_SHIFT_TABLE(b BIT(16));
INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000');
INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE;
INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE;
INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE;
INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE;
SELECT POSITION(B'1101' IN b),
       POSITION(B'11011' IN b),
       b 
       FROM BIT_SHIFT_TABLE ;
 position | position |        b         
----------+----------+------------------
        1 |        1 | 1101100000000000
        2 |        2 | 0110110000000000
        3 |        3 | 0011011000000000
        4 |        4 | 0001101100000000
        5 |        5 | 0000110110000000
        6 |        6 | 0000011011000000
        7 |        7 | 0000001101100000
        8 |        8 | 0000000110110000
        9 |        9 | 0000000011011000
       10 |       10 | 0000000001101100
       11 |       11 | 0000000000110110
       12 |       12 | 0000000000011011
       13 |        0 | 0000000000001101
        0 |        0 | 0000000000000110
        0 |        0 | 0000000000000011
        0 |        0 | 0000000000000001
(16 rows)

CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20));
INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011');
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE;
SELECT POSITION(B'1101' IN v),
       POSITION(B'11011' IN v),
       v 
       FROM VARBIT_SHIFT_TABLE ;
 position | position |          v           
----------+----------+----------------------
        1 |        1 | 11011
        2 |        2 | 011011
        3 |        3 | 0011011
        4 |        4 | 00011011
        5 |        5 | 000011011
        6 |        6 | 0000011011
        7 |        7 | 00000011011
        8 |        8 | 000000011011
        9 |        9 | 0000000011011
       10 |       10 | 00000000011011
       11 |       11 | 000000000011011
       12 |       12 | 0000000000011011
       13 |       13 | 00000000000011011
       14 |       14 | 000000000000011011
       15 |       15 | 0000000000000011011
       16 |       16 | 00000000000000011011
(16 rows)

DROP TABLE BIT_SHIFT_TABLE;
DROP TABLE VARBIT_SHIFT_TABLE;