Valentine’s Day

Valentine’s Day is coming up and it is time to get prepared. In the past, I had written about how to impress your spouse and how to date with Db2. Today, I want to expand on this, really. I am going to show you how to teach Db2 to expand bits and bytes into ASCII art. Some input data and some simple SELECT statement and, voila, Db2 shows a heart and its love to whoever is seeing it. So let’s get started…

The following creates a table “myarts” to hold some encodings. The core is the encoded “line_data” which is prepared with the INSERT statements.

CREATE TABLE myarts (
    art_id INTEGER NOT NULL,
    art_name VARCHAR(10) NOT NULL,
    line_number INTEGER NOT NULL,
    line_data SMALLINT,
    PRIMARY KEY (art_id, line_number)
);

INSERT INTO myarts VALUES (1, 'Heart', 1, 0);
INSERT INTO myarts VALUES (1, 'Heart', 2, 1820);
INSERT INTO myarts VALUES (1, 'Heart', 3, 4030);
INSERT INTO myarts VALUES (1, 'Heart', 4, 8191);
INSERT INTO myarts VALUES (1, 'Heart', 5, 8191);
INSERT INTO myarts VALUES (1, 'Heart', 6, 4094);
INSERT INTO myarts VALUES (1, 'Heart', 7, 2044);
INSERT INTO myarts VALUES (1, 'Heart', 8, 1016);
INSERT INTO myarts VALUES (1, 'Heart', 9, 496);
INSERT INTO myarts VALUES (1, 'Heart', 10, 224);
INSERT INTO myarts VALUES (1, 'Heart', 11, 64);
INSERT INTO myarts VALUES (1, 'Heart', 12, 0);
INSERT INTO myarts VALUES (1, 'Heart', 13, 0);

Some lovely SQL recursion

With the table in place, it is time again for some SQL recursion:

WITH bits(bit_pos) AS (
    VALUES(0)
    UNION ALL
    SELECT bit_pos + 1
    FROM bits
    WHERE bit_pos < 63
),
expanded_bits AS (
    SELECT 
        m.line_number,
        b.bit_pos,
        CASE 
            WHEN BITAND(
                m.line_data, POWER(2, b.bit_pos)
            ) >0 THEN '*'
            ELSE ' '
        END AS char_value
    FROM myarts m, bits b
    WHERE b.bit_pos <(select max(line_number) from myarts where art_id=1)
    AND m.art_id=1 
)
SELECT 
    line_number,
    CAST(LISTAGG(char_value) WITHIN GROUP (ORDER BY bit_pos DESC) as VARCHAR(20)) AS ascii_line
FROM expanded_bits
GROUP by line_number
ORDER BY line_number;

We first create a table with bit positions from 0 to 63 (for 64x64 ASCII art). Then, we iterate over the bits for each row and either return ‘*’ or blank (’ ‘). Last, we lump (concatenate) the characters for each row together via LISTAGG and cast it to a 20 bytes string. The function BITAND is used to test the bit positions, POWER to create the number to test with for that bit position.

In my Db2 12.1 Community Edition, the query returns the following result:

LINE_NUMBER ASCII_LINE          
----------- --------------------
          1                     
          2   ***   ***         
          3  ***** *****        
          4 *************       
          5 *************       
          6  ***********        
          7   *********         
          8    *******          
          9     *****           
         10      ***            
         11       *             
         12                     
         13                     

  13 record(s) selected.

Conclusions

Db2 = ❤️❤️❤️.

If you have feedback, suggestions, or questions about this post, please reach out to me on Mastodon (@data_henrik@mastodon.social) or LinkedIn.