【ORACLE】墨天轮SQL祝福大赛后续的一点研究

春节前,墨天轮发起了一个新年SQL“祝福”大赛,https://www.modb.pro/db/241365
本人有幸获得了最佳创意奖
【恭贺新春】SQL还能这样玩之我用sql写福字

文章里使用了各种好玩的方法,比如sql调用python、sql检索汉字点阵字库、递归sql、sql进行base64、sql进行gzip压缩、12c/18c/21c新特性等等。当时为了写这篇文章想了不少内容,其中就有个想法,点阵字转01二进制文本->转二进制数字->转十六进制文本->转二进制数据->转base64编码,但是当时没想出合适的二进制数值与十六进制数值互转的方法,因此这个想法就没放上去。此事还引发我写了一篇这样的文章
【ORACLE】对二进制字符串的存储空间占用进行压缩的研究

今天在网上偶然间看到一篇2007年的帖子
http://www.itpub.net/thread-717913-1-1.html

有个版主回复了一个sql,是十进制转二进制

select ltrim(translate(ltrim(dump(convert(translate(to_char(235, 'FMXXXXXXXX'),
                                                    '0123456789ABCDEF',
                                                    '热壬纫扔扇缮梢捎胰疑乙矣尤由右佑'),
                                          'zhs16gbk')),
                             'TypLen =0123456789'),
                       '123456789, :',
                       '1'),
             '0')
  from dual;

我看后,大感震撼,原来还能这样！利用gbk字符集中这几个特殊位置的汉字,对每个汉字的2个字节,分别转换成十进制(200,201,210,211),再把十进制数值中的2去掉,只剩下1和0,拼起来就是二进制文本了。其实这和把16个十六进制数字用case when去对应二进制数字是类似的逻辑,不过那样写的话,sql就太长了。

然后我就借用了这16个汉字,用另一种方式来获取对应的字节数据,避免产生大量无用的中间字符,最后用此方式写出的福字sql如下

WITH R AS
 (SELECT RAWTOHEX(convert(translate(RAWTOHEX(UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw('EHAJgABwGZDpMAnAEDw7xFJ0ksQSRBH4EAg='))),
                                    '0123456789ABCDEF',
                                    '热壬纫扔扇缮梢捎胰疑乙矣尤由右佑'),
                          'zhs16gbk')) R
    FROM DUAL),
T(H,
P) AS
 (SELECT TRANSLATE(TO_NUMBER(SUBSTR(R, 1, 2), 'XX'), '12', '1') H, 1 P
    FROM R
  UNION ALL
  SELECT TRANSLATE(TO_NUMBER(SUBSTR(R, P * 2 + 1, 2), 'XX'), '12', '1'),
         P + 1
    FROM T, R
   WHERE P <= 103),
B AS
 (select LISTAGG(H) B from T),
L(C,
LINE) AS
 (SELECT SUBSTR(B.B, 1, 16) C, 1 LINE
    FROM B
  UNION ALL
  SELECT SUBSTR(B.B, LINE * 16 + 1, 16), LINE + 1
    FROM L, B
   WHERE LINE <= 12)
select LISTAGG(TRANSLATE(C, '01', ' ○'), CHR(10)) from L

这个sql中,唯一需要变动的输入参数,只有"EHAJgABwGZDpMAnAEDw7xFJ0ksQSRBH4EAg="这一串base64编码,也就是说,对于任何一个16*16的点阵字,都可以压缩成几十个字符的base64编码文本,如果是笔画少的,估计可以压得更小了(此例中,我去掉了点阵字里没有点的3个空行)。

还是刚刚那个帖子,我往后翻了一页,发现这个版主又给了一种十进制转二进制的方法:

select ltrim(translate(asciistr(translate(to_char(185, 'FMXXXXXX'),
                                          '0123456789ABCDEFG',
                                          '业丛个丫会伛伪伫帚帛帪師弚弛弪弫')),
                       '4E1A5F2B\',
                       '00001111'),
             '0')
  from dual;

这脑洞真让人佩服,我也想过用asciistr函数来找"0000","0001"这样的字,但是很明显这些字符中存在不可见字符,有的甚至无法复制,但这个版主想到一个奇葩方案,用其他字符来替代0和1,于是我又有了下面这个sql

with b as
 (select translate(asciistr(translate(RAWTOHEX(UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw('EHAJgABwGZDpMAnAEDw7xFJ0ksQSRBH4EAg='))),
                                      '0123456789ABCDEFG',
                                      '业丛个丫会伛伪伫帚帛帪師弚弛弪弫')),
                   '4E1A5F2B\',
                   '00001111') b
    from dual),
L(C,
LINE) AS
 (SELECT SUBSTR(B.B, 1, 16) C, 1 LINE
    FROM B
  UNION ALL
  SELECT SUBSTR(B.B, LINE * 16 + 1, 16), LINE + 1
    FROM L, B
   WHERE LINE <= 12)
select LISTAGG(TRANSLATE(C, '01', ' ○'), CHR(10)) from L

然后,如果有点阵字了,如何生成这一串base64编码呢？

首先需要有二进制转十六进制的方法,对于数据比较小的,网上已经有很多方法了,或者也可以使用我这篇文章里最后写的函数binstr_to_hexblob 【ORACLE】对二进制字符串的存储空间占用进行压缩的研究
然后将01点阵字复制到下面这个sql里执行即可

with t as (select replace(
'0001000001110000
0000100110000000
0000000001110000
0001100110010000
1110100100110000
0000100111000000
0001000000111100
0011101111000100
0101001001110100
1001001011000100
0001001001000100
0001000111111000
0001000000001000'
,chr(10)) t from dual)
select utl_raw.cast_to_varchar2( utl_encode.base64_encode(binstr_to_hexblob(t))) from t;