春节前,墨天轮发起了一个新年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;
注:本篇纯粹是逻辑研究,且某些代码存在效率低下的问题,因此不适用于正式编码场合下使用!
- 本文链接: https://www.darkathena.top/archives/sql-draw-chinese-fu-2
- 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!