Stuart Cooper wrote:
>> I'd like to convert very large unsigned numbers (ala bigint) to a text
>> string using base62. I created this PL/PERL function to do the trick:
>
> base 62 is cruel and unusual punishment. Introduce two more printing
> characters to your set a..z, A..Z, 0..9 such as "_" and "!" and do it in base 64
> instead. There's probably plenty of standard tools and convertors to do
> things in base 64.
I thought about adding 2 more characters, but I didn't like anything
that was on my keyboard ;-) In English, we use "0-9a-zA-Z" commonly but
to me, "-" and "_" look odd at the beginning or end of a string or when
repeated more than once.
Ugly code:
AR-____--_
OK code:
ARzd1A0b3P
In some cases, I may even want to eliminate characters that look similar
like "1" and "l" or "O" and "0". Better yet, if the code that comes out
of the conversion contains vowels, its possible to look like profanity:
PzbigAss22
So, perhaps a better character set would not include vowels either:
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
is reduced to:
23456789bcdfghjkmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ
# SELECT LENGTH('23456789bcdfghjkmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ');
length
--------
49
(1 row)
This looks like a job for Base49 conversion ;-)
The code I attached can do anywhere from base2 through base62 without
problems but uses a hard-coded replacement set of characters. This
could be modified to accept another parameter of replacement chars.
My original question is more along the lines of trying to see if there
were built-in functions in PostgreSQL that already do this type of base
conversion. As a built-in, my expectation would be that it would likely
be faster and supported ... without me having to introduce a PL/PERL
dependent custom function. Worst case, I could do the base conversion
using the default character mappings and just remap the output to the
alternate characters.
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM
23456789bcdfghjkmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ
0 => 2, 1 => 3. ... r => z, ... M => Z
Base conversion seems like a common task for most programming languages.
I didn't know where to look and it wasn't coming up in my searches.
-- Dante