sql - 使用纯 SQLite 将字符串转换为二进制并返回

Question

我知道如何将字符串转换为其二进制表示。

WITH string(s) AS (VALUES('string')),
     bytes(i, j, b, s) AS (
        VALUES(1, 0, '', (SELECT s FROM string))
        UNION ALL
        SELECT i + 1, j + 1, UNICODE(SUBSTR(s, i, 1)), s FROM bytes
        LIMIT (SELECT LENGTH(s) + 1 FROM string)
     ),
     octets(i, o) AS (
        VALUES(1, '')
        UNION ALL
        SELECT i + 1, '' FROM octets
        LIMIT (SELECT COUNT(b) FROM bytes WHERE b <> '')
     )
SELECT replace(group_concat((
   WITH bin(n, c) AS (
      VALUES(7, '')
      UNION ALL
      SELECT n - 1, CASE (b >> n) & 1
                       WHEN 1 THEN '1'
                       ELSE '0'
                    END FROM bin
      LIMIT 9
   ) SELECT replace(group_concat(c), ',', '') FROM bin
)), ',', '')
FROM bytes b
JOIN octets o
ON b.j = o.i;

结果是 "011100110111010001110010011010010110111001100111" .是否可以使用纯 SQLite 将它们转换回常规 View ？这需要临时表还是可以通过嵌套 WITH RECURSIVE 解决？收缩？

Answer 1

您应该创建函数或在应用程序级别执行此操作，但让我们尝试使用纯 SQL:

WITH RECURSIVE cnt(x) AS (
     SELECT 1
     UNION ALL
     SELECT x+1 FROM cnt
     LIMIT 100
), bin(b) AS(
   VALUES ('011100110111010001110010011010010110111001100111')
  )
SELECT 
    GROUP_CONCAT(CHAR(
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 1, 1) * 128 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 2, 1) * 64 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 3, 1) * 32 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 4, 1) * 16 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 5, 1) * 8 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 6, 1) * 4 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 7, 1) * 2 +
    SUBSTR(SUBSTR(b, 1+(c.x-1)*8, 8), 8, 1) * 1 
    ), '') AS result
FROM bin b
JOIN cnt c
  ON c.x <= LENGTH(b)/8;

DBFiddle Demo

这个怎么运作:

cnt(x) 计数/数字表

bin(b) 我们的二进制表示

主要查询

将 48 个字符分成 8 个 block

转换为数字

CHAR -> 获取字符串表示

group_concat - 获取一个字符串

当然还有很多改进的地方。