Open martindsouza opened 6 years ago
I would call it to_base36
.
I found that enumerating the symbols in advance, and exchanging the MOD for subtraction gives a little perf boost. Around 15% on my machine.
create or replace function basex2 (
p_num in integer,
p_base in integer)
return varchar2
as
l_symbols varchar2(64) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
l_return varchar2(255);
l_quotient integer := p_num;
l_trunced_part integer;
l_remainder integer;
begin
while l_quotient > 0 loop
l_trunced_part := trunc(l_quotient / p_base);
l_remainder := l_quotient - l_trunced_part*p_base;
l_quotient := l_trunced_part;
l_return := substr(l_symbols,l_remainder+1,1) || l_return;
end loop;
return l_return;
end;
In regards to @dmcghan suggestion on Twitter, this file from PWGen has the list of ambiguous characters they use.
http://pwgen.cvs.sourceforge.net/viewvc/pwgen/src/pw_rand.c?view=markup
const char *pw_ambiguous = "B8G6I1l0OQDS5Z2";
I'd suggest to add lowercase oisz
, as well but excluding everything from that list may be a bit too aggressive.
Help Wanted!
Please read through this and need help to determine what to call these function(s)
oOlL
etc.Problem
Sometimes business users want a transaction number. Ex: invoice number.
What most people do is create a sequence and then pad it with some
0
s. Ex:0081
. The problem with it is what happens when we hit9999
? We'll then have 4 character and 5 character transaction numbers.A simple solution is to covert a sequence to hex , thus giving transaction numbers like
A08F
. Though this helps, in high transaction systems adding 6 additional characters isn't enough or requires that transaction numbers are purposely long. See table below for statsThe following table shows how many values you can get for the number of characters:
Solution
The proposed solution is to allow for transaction numbers that cover
0-Z
. I.e.0,1,...9,A,B...Z
for each character (base 36). We could expand this in the future to go beyond base 36 but would need to defined what the 37th character would look like.The following query converts a number to base 36:
PL/SQL version:
Tasks
dec2base
function (better name required)p_length
andp_pad
(either both required or both not required). Note: thinking of this we may require it sincedec2base
would handle the unpadded versionp_base
is between 10 and 36p_x
is a whole number > 0oOlL
that may be hard to decipher.p_base = length(p_char_mapping)