14

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

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
dan
  • 43,914
  • 47
  • 153
  • 254

3 Answers3

20

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Unfortunately, the linked blog post is dead. You don't happen to have such an algorithm ready for an `IN digits bytea` representation for a longer number? :-) – Lukas Eder Oct 02 '13 at 15:09
  • @LukasEder: I'm not sure what you're asking. You have a `bytea` column that you want to encode as base-36 inside the database? – mu is too short Oct 02 '13 at 16:55
  • if you have a bytea you want something that encodes binary data with a sensible block size like `base32` instead. base36 gets just over 31 bits into 6 characters, great for signed `int32_t`, not so useful for binary data. a block size of 31 bytes=48 characters is kind of unweildy. – Jasen May 23 '18 at 03:30
  • you are correct, while I appreciate the solution, this sorta stuff should not be coded as functions into the db – PirateApp Mar 01 '19 at 07:35
  • You could use the `abs` function so save a few lines. – Pan P Apr 08 '19 at 13:34
  • This function is beautiful! – user1079877 Nov 12 '19 at 16:47
0

here's a version that can take numbers of any size, it uses the data type "numeric" which is the postgresql implementation of bignum.

CREATE OR REPLACE FUNCTION base36_encode(IN digits numeric, IN min_width int = 0) RETURNS text AS $$
DECLARE
    chars char[] := 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' ] ;  
    ret text:=''; 
    val numeric:= digits; 
BEGIN
    IF digits < 0 THEN 
        val := -val;
    END IF; 

    WHILE val > 0 OR min_width > 0 LOOP 
        ret := chars[(mod(val,36))+1] || ret; 
        val := div(val,36); 
        min_width := min_width-1;
    END LOOP;
    IF digits < 0 THEN 
        ret := '-'||ret; 
    END IF; 
    RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
Jasen
  • 11,837
  • 2
  • 30
  • 48
0

Modified Implementation

Modified from other implementation to increase the readability. Any kind of update or modification or suggestion appreciated to increase the readability.

CREATE OR REPLACE FUNCTION fn_base36_encode(IN base10 bigint)
    RETURNS  varchar
    LANGUAGE plpgsql
AS $BODY$
DECLARE
    base36 varchar := '';
    intval bigint  := abs(base10);
    char0z char[]  := regexp_split_to_array('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '');
BEGIN
    WHILE intval != 0 LOOP
        base36 := char0z[(intval % 36)+1] || base36;
        intval := intval / 36;
    END LOOP;

    IF base10 = 0 THEN base36 := '0'; END IF;
    RETURN base36;
END;
$BODY$;
Śhāhēēd
  • 1,812
  • 6
  • 23
  • 44