Use T-SQL to Generate a Sequence

Downloads
136376.zip

I recently received an inquiry from a reader asking about various tasks concerning vehicle license plates: specifically, how to generate a sequence of license plates, how to identify ranges of existing license plates (islands), and how to identify ranges of missing license plates (gaps). While working on these tasks, I found them to be interesting T-SQL exercises and therefore decided to present them as reader challenges. In this article, I focus on the first task: generating a sequence of license plates. I’ll leave the other tasks for you to try on your own.

 

The Challenge

Our challenge for this article involves generating a sequence of license plates between two input license plate numbers, for an input license plate type. By type, I’m referring to the fact that in different locations (e.g., different states in the United States), as well as in certain locations in different time periods, the rules can be different. For example, currently the license plate system in Washington state uses the form AAANNNN (A = alpha, N = numeral), whereas the current system in New Jersey uses the form ANN-AAA. Some systems have a straightforward right-to-left significance order (from least to most) for the characters (e.g., in Washington state, the order is 7654321, where 1 represents the least significant character). Other systems have a more specialized order (e.g., in New Jersey, the order is 321-654). Also, some systems don’t use certain letters in certain character positions. As an example, see the Wikipedia article “Vehicle registration plates of New Jersey” at en.wikipedia.org/wiki/Vehicle_registration_plates_of_New_Jersey. Note that the New Jersey license plate system that was first issued in 2007 didn’t use the letters D, T, or X in the first position or the letters I, O, or Q in any position.

The specifics of the challenge are that you need to develop a solution that allows storing in the database the different rules for each license plate system that you need to support. You also need to develop a table function that returns a sequence of license plates between two input plates, based on an input plate type.

For the purposes of this challenge, we’ll keep things simple. Suppose that you need to support two plate system types called WA2009 and NJ2010. The system type WA2009 has the following rules:

• Plate form: AAANNNN (three alphas followed by four numerals)
• Character significance order where 1 means least significant: 7654321
• No unsupported characters (namely, numeral range is 0-9 and alpha range is A-Z)

So, for example, the range AAA0000 through AAA9999 is followed by the range AAB0000 through AAB9999.

The system type NJ2010 has the following rules:

• Plate form: ANN-AAA (alpha, two numerals, dash, three alphas)
• Character significance order: 321-654
• For the sake of the challenge, assume similar character restrictions as in the NJ2007 system; namely, don’t use the letters D, T, or X in the first position or the letters I, O, or Q in any position

So, for example, the sequence starts with the range A00-AAA through Z99-AAA, and this range is followed by A00-AAB. Also, with the character restrictions, C99-AAA, for example, is followed by E00-AAA, because D isn’t used in the first position.

To test your solution, you can use the following code to request a sequence of WA2009 plates:

You should get a sequence with 260,000 plates, shown in abbreviated form in Figure 1.

Figure 1: Sequence of WA2009 Plates

You can use the following code to request a sequence of NJ2010 plates:

You should get a sequence of 46,000 plates, shown in abbreviated form in Figure 2.

Figure 2: Sequence of NJ2010 Plates

I suggest that you try to come up with your own solution before looking at mine.

 

The Solution

The approach I used in my solution is to create two mapping functions: one (call it PlateToValue) that maps a license plate to an integer value that reflects the position of the current license plate in the entire sequence, and another (call it ValueToPlate) that maps an integer value to the license plate that it represents. I created a table called PlateChars in which I stored the plate system rules, and I used this table in the functions.

Use the code in Listing 1 to create and populate the PlateChars table. The platetype attribute represents the plate system type (e.g., 'WA2009', 'NJ2010'); charpos represents the position of the character in the string (1 is leftmost); validchars holds a string representing the valid symbols in the current character position; charvalue is the multiplier of the digit (0-based ordinal) that the symbol in the current character position represents.