Skip to content

Instantly share code, notes, and snippets.

@edwardsharp
Forked from btbytes/base36_encode_decode.sql
Created December 14, 2017 19:27
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 edwardsharp/aad2771f52d087df891c7be142b31abd to your computer and use it in GitHub Desktop.
Save edwardsharp/aad2771f52d087df891c7be142b31abd to your computer and use it in GitHub Desktop.
Base36 Conversion in PostgreSQL
-- source: http://www.jamiebegin.com/base36-conversion-in-postgresql/
CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
RETURNS varchar AS $$
DECLARE
chars char[];
ret varchar;
val bigint;
BEGIN
chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F','G','H','I','J','K','L','M'
,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
val := digits;
ret := '';
IF val < 0 THEN
val := val * -1;
END IF;
WHILE val != 0 LOOP
ret := chars[(val % 36)+1] || ret;
val := val / 36;
END LOOP;
IF min_width > 0 AND char_length(ret) < min_width THEN
ret := lpad(ret, min_width, '0');
END IF;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
RETURNS bigint AS $$
DECLARE
a char[];
ret bigint;
i int;
val int;
chars varchar;
BEGIN
chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
FOR i IN REVERSE char_length(base36)..1 LOOP
a := a || substring(upper(base36) FROM i FOR 1)::char;
END LOOP;
i := 0;
ret := 0;
WHILE i < (array_length(a,1)) LOOP
val := position(a[i+1] IN chars)-1;
ret := ret + (val * (36 ^ i));
i := i + 1;
END LOOP;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment