Converting from base-16 to base-36 number (with 128bit precision)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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