PostgreSQL: Is there a function that will convert a base-10 int into a base-36 string?

Question

Is there a function in PostgreSQL that can convert a base 10 number like 30 into a base 36 representation like u?

Answer 1

There are base-64 functions (such as encode) but nothing for base-36. But you could write one of your own or use this one:

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;

I think you should ask yourself if the database is the right place for dealing with this sort of data formatting though, presentational issues like this might be better handled closer to final viewing level of your stack.