Skip to content

Instantly share code, notes, and snippets.

@andrewodri
Created May 23, 2023 17:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save andrewodri/f63e6d0e27406d172ba330560c5e8565 to your computer and use it in GitHub Desktop.
Save andrewodri/f63e6d0e27406d172ba330560c5e8565 to your computer and use it in GitHub Desktop.
Convert SQLite TEXT to underlying binary representation of unicode data as INTEGER
WITH RECURSIVE bytes(i, byte) AS (
VALUES(1, 0)
UNION ALL
SELECT i + 1, (
WITH code_points(code_point) AS (
VALUES(0)
UNION ALL
SELECT (UNICODE(
SUBSTR('hey', i * -1, 1)
) << (i - 1) * 8) FROM code_points LIMIT 2
) SELECT sum(code_point) FROM code_points
) AS byte_column FROM bytes WHERE byte_column > 0
) SELECT sum(byte) FROM bytes;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment