Back in the late 1970's, the license plate on my car read "CAR 999". I always liked that my car's plate read C-A-R. Creating the numbers from 001 though 999 is pretty easy, but creating the letters from AAA through ZZZ is a little trickier. In this post, we'll discuss how to create unique identification numbers, and we'll virtually re-create the late1970's State of Maryland license plates with three letters followed by three numbers.
The license plate problem is one example of generating unique identification numbers. We go to school, we get an id. We go to work, we get an id. We have an id for the computer, we have an id on our driver's license, we have a Social Security number, and our insurance policies have id numbers. Sometimes the ids are just numbers, but more often the id is a mix of letters and numbers. Using letters instead of or in addition to numbers greatly expands the number of ids. On a car's license plate, the letters and numbers take the same amount of space. But, the letters from AAA to ZZZ give us 17,576 possible combinations, while the numbers from 000 to 999 give us only 1000 combinations. Using only numbers, we quickly run out of identification numbers.
When we create ids, we can not reuse a given sequence of letters and numbers. We need to know what we used previously. There are a few ways of tackling this problem. In our license plate example, we could pre-generate all the possible plate numbers, store them in a table, and mark them "in-use" as we use them. Or, we could store the last plate in a table, generate the next plate based on the last plate, and update the table with the latest plate. But, the easiest solution is to use database sequences. A database sequence is an object defined in the database that automatically increments each time we consume a number. We don't have to pre-generate plate numbers, we don't have to update tables or keep track of anything. The database does the work for us. And that's a good thing.
We'll start by creating two sequences, one for the letters and one for the numbers. More about why the letters start with 676 and finish with 17575 later:
create sequence numbers_seq start with 1 maxvalue 999 cycle nocache; create sequence letters_seq start with 676 maxvalue 17575 nocache cycle;
We can use the number_seq directly, but we must convert the letter_seq to three characters. If we think about the letters of the alphabet as a base 26 counting system, then we need to convert base 10 digits to base 26 digits. We learned to do this in Computer Science 101, when we converted base 10 numbers to base 16 numbers and back again. The solution is the same, only the base is different. This function accepts a base 10 number and returns a three-character base 26 representation:
create or replace function seq2letters(p_seq in number)
return varchar2 is
v_letters varchar2(3) default null;
v_n integer := p_seq;
v_r integer;
digits varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
base integer := length(digits);
begin
while ( v_n > 0) loop
v_r := mod(v_n,base);
v_letters := substr(digits,v_r + 1, 1) || v_letters;
v_n := floor( v_n / base );
end loop;
return v_letters;
end seq2letters;
For debugging purposes, we'll create the a function to do the opposite conversion, too. When we decide on the start and maxvalue values for the letter_seq, this is useful for finding out what the base 10 numbers are for BAA and ZZZ.
create or replace function letters2num( p_letters in varchar2)
return number
is
v_length integer := length(p_letters);
v_num number := 0;
v_i integer;
digits varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
base integer := length(digits);
v_digit varchar2(1);
v_digit_value integer;
v_power integer := 0;
begin
for v_i in reverse 1 .. v_length loop
v_digit := substr(p_letters,v_i,1);
v_digit_value := instr(digits, v_digit);
v_num := v_num + (v_digit_value - 1) * base ** v_power;
v_power := v_power + 1;
end loop;
return v_num;
end letters2num;
Let's test our two new functions. We want to find the base 10 equivalents of BAA and ZZZ using letters2num. Then, we'll test seq2letters to make sure we get our starting letters back.
select letters2num('BAA') start, letters2num('ZZZ') finish
from dual;
START FINISH
----- ------
676 15757
select seq2letters(676) first_plate, seq2letters(15757) last_plate
from dual;
FIRST_PLATE LAST_PLATE
----------- ----------
BAA ZZZ
Perfect! We have the tools to translate numbers between base 10 and base 26.
Next, we'll create two functions that return the next letters and next numbers. Our function will get the next number seq and return a 3-digit number. The letters function is a little more complicated. Whenever the plate number is 1, we need to get the next letters. If the plate number is not 1, then we reuse the current letters. In either case, the letter function will use the base 10 converter function to convert the sequence to three letters. Here's the code
create or replace function get_numbers return number is
begin
return numbers_seq.nextval;
end get_numbers;
create or replace function get_letters ( p_num number ) return varchar is
v_letters_10 number;
v_letters_26 varchar2(3);
begin
if p_num = 1 then
v_letters_10 := letters_seq.nextval;
else
select last_number - 1
into v_letters_10
from user_sequences
where sequence_name = 'LETTERS_SEQ';
end if;
v_letters_26 := seq2letters(v_letters_10);
return v_letters_26;
end get_letters;
Now we have all the code we need to generate a license plate. First we call the numbers function, then we call the letters function. In this demo, hosted on Oracle's APEX demonstration website, we'll create a 1970's Maryland license plate. And what does a vintage late 1970s Maryland plate look like? Check eBay: it's more than an auction site, it's a museum as well! The old plates were pretty simple: large red letters and numbers with a red border around the whole plate. Try out the demonstration, every click of the Make a Plate button makes another license plate. Try running the demonstration in two or more windows, you'll never make the same plate twice. Well, at least not until you get to plate ZZZ 999 and the sequence roll over.
1. American euphemisms for going to prison.
