Posted by --CELKO-- on 09/30/61 12:00

>> The data is being sorted, but not in the way I desire it to be. <<

You did not bother to tell us HOW you want it sorted or what the table
looks like. My guesses are:

(1) You want to first sort by some undefined alpha prefix that can be
upper and lowercase mixed (no rules for how to handle that were given)
and an integer postfix that should have been in its own column in the
table. Redesign the table and split this into two columns. You then
do an ORDER BY on both columns.

(2) The encoding is poorly designed and you need to add zeros in the
string, and validate it it in the DDL. Clean up the existing data,
then add a constraint to prevent a repetition of the problem,
something like this:

CREATE TABLE Products
(..
product_name CHAR(7) NOT NULL
CHECK (product_name LIKE '[A-Z'][a-z][a-z][a-z]-[0-9][0-9]'),
..);

In short, the problem is in the data and not in the procedural code.

[Back to original message]