Tuesday, 30 June 2009

Convert integers to base36 in PostgreSQL

This is PostgreSQL function for converting integers to base36 numbering system:

CREATE OR REPLACE FUNCTION dec2base36(bigint)
RETURNS character varying AS'

DECLARE
inparam alias FOR $1;
invalue bigint;
CLIST char ARRAY[35] :='{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}';
rv varchar :=''; -- rv = resulting value

BEGIN
invalue = inparam;

WHILE invalue != 0 LOOP
rv = (CLIST[((invalue % 36)+1)]) || rv;
invalue = (invalue/36);
END LOOP;

RETURN(rv);
END;'
language 'plpgsql';


EXAMPLE:
=> SELECT dec2base36(123456789);

-[ RECORD 1 ]------
dec2base36 | 21I3V9

No comments: