Instantly share code, notes, and snippets.

Embed
What would you like to do?
Converting from base-16 to base-36 number (with 128bit precision)
-- convert from base-16 to base-36 (128bit precision)
CREATE FUNCTION conv(_hex TEXT)
RETURNS TEXT
BEGIN
DECLARE _hex_len TINYINT;
DECLARE _dec DECIMAL(65);
DECLARE _chars CHAR(36);
DECLARE _base36 TEXT;
DECLARE _mod TINYINT;
SET _hex_len = LENGTH(_hex);
IF (_hex_len > 32) THEN
SIGNAL SQLSTATE '40001' SET MESSAGE_TEXT = 'Hex is out of range (max 32 chars)';
END IF;
-- convert from base-16 to base-10
SET _dec = CAST(CONV(SUBSTRING(_hex, -LEAST(16, _hex_len)), 16, 10) AS DECIMAL(65));
IF _hex_len > 16 THEN
SET _dec = _dec + CAST(CONV(SUBSTRING(_hex, 1, _hex_len - 16), 16, 10) AS DECIMAL(65)) * 18446744073709551616;
END IF;
-- convert from base-10 to base-36
SET _chars = "0123456789abcdefghijklmnopqrstuvwxyz";
SET _base36 = "";
WHILE _dec > 0 DO
SET _mod = _dec % 36;
SET _base36 = CONCAT(SUBSTRING(_chars, _mod + 1, 1), _base36);
SET _dec = FLOOR(_dec / 36);
END WHILE;
return _base36;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment