Complicated ORDER BY needed to sort irregular values

Question

I am trying to develop a system to display products from a database on a webpage. Normally this is no problem, except that one manufacturer has several abnormal part numbers I need to sort by properly.

Normally, I could just use an invisible column to sort things out, but then that makes inserting new items in-between two older ones much more difficult.

For example, here are some of the part numbers:

1211
1225
14-302
14-303
2015
23157
3507
35280UP
42-3309
42-3312
4241

Now, the normal order by mfgr produces the above order. What it SHOULD be is something more like this:

14-302
14-303
42-3309
42-3312
1211
1225
2015
3507
4241
23157
35280UP

What is going to be my best bet on sorting this properly? If they were just being made in a csv file and uploaded afterwards this wouldn't be a problem. But because of automatic database changes, the server will be modifying values in real time. So manually updating it is out of the question. This means that a back end will be required to insert new items, but by what method could I insert an item between another? I would prefer to not resort to something like decimals to give me X in between values (Like I have 1.00 and 2.00 and I want to put another between them so I make it 1.50).

Any help would be appreciated.

EDIT:

The way I would like to sort it is this: if it has a hyphen, ie 14-302, it is sorted by the 14, and then any 14-xxx is sorted by the numbers after the hyphen. then, just numbers would be sorted by their actual numner, 802 comes before 45768. Then any number that has a letter(s) after it will be sorted by the number, the the letter so 123a comes before 123b but after 122. And 123b comes before 124c. and lastly anything that begins with an M- will be sorted last, and by the numbers after the hyphen.

Answer 1

With your adjusted question the principle is still the same. My first instinct was to go with extra field but you could use stored function for those. You still need to divide your partnumber in 3 parts (I take it it's not more than that).

• Part1 is an INTEGER and has the first number if existing else fill it with maxint (largest for part1).
• Part2 is a CHAR and contains letters if existing.
• Part3 is an INTEGER containing the second number if existing.

You can sort by calling a function for these values.

Here is the complete source: For your convenience a link to a working SQL Fiddle. It's really sloppy/fast programming but it works. Put together in little time and i'm sure there are points for improvement. You can tweak it yourself. Later you can delete the part1, part2 and part3 from the view. (but leave it in the order by) It's only to show how the sort is done.

DROP PROCEDURE IF EXISTS `uGetParts`//
DROP FUNCTION IF EXISTS `uExtractPart1`//
DROP FUNCTION IF EXISTS `uExtractPart2`//
DROP FUNCTION IF EXISTS `uExtractPart3`//

CREATE PROCEDURE `uGetParts`(
  IN ins varchar(50),
  OUT num1 int unsigned,
  OUT num2 int unsigned,
  OUT num3 int unsigned)
NO SQL
BEGIN
  SET num1=0;
  SET num2=0;
  SET num3=0;
  WHILE (num1<length(ins)) AND
    (SUBSTRING(ins,num1+1,1) REGEXP('(^[0-9]+$)')=1) DO
      SET num1=num1+1;
  END WHILE;
  SET num2=num1;
  WHILE (num2<length(ins)) AND
    (SUBSTRING(ins,num2+1,1) REGEXP('(^[0-9]+$)')=0) DO
      SET num2=num2+1;
  END WHILE;
  SET num3=num2;
  WHILE (num3<length(ins)) AND
    (SUBSTRING(ins,num3+1,1) REGEXP('(^[0-9]+$)')=1) DO
      SET num3=num3+1;
  END WHILE;
END//

CREATE FUNCTION `uExtractPart1`(ins varchar(50))
RETURNS int unsigned NO SQL
BEGIN
  DECLARE num1 INT default 0;
  DECLARE num2 INT default 0;
  DECLARE num3 INT default 0;
  call uGetParts(ins,num1,num2,num3);
  IF num1>0 THEN       
    RETURN CAST(SUBSTRING(ins,1,num1) AS UNSIGNED);
  ELSE
    RETURN ~0 >> 32;
  END IF;  
END//

CREATE FUNCTION `uExtractPart2`(ins varchar(50))
RETURNS varchar(50) NO SQL
BEGIN
  DECLARE num1 INT default 0;
  DECLARE num2 INT default 0;
  DECLARE num3 INT default 0;
  call uGetParts(ins,num1,num2,num3);
  IF num2>num1 THEN       
    RETURN SUBSTRING(ins,num1+1,num2-num1);
  ELSE
    RETURN '';
  END IF;  
END//      

CREATE FUNCTION `uExtractPart3`(ins varchar(50))
RETURNS int unsigned NO SQL
BEGIN
  DECLARE num1 INT default 0;
  DECLARE num2 INT default 0;
  DECLARE num3 INT default 0;
  call uGetParts(ins,num1,num2,num3);
  IF num3>num2 THEN       
    RETURN CAST(SUBSTRING(ins,num2+1,num3-num2) AS UNSIGNED);
  ELSE
    RETURN 0;
  END IF;  
END//

You can call it like this:

SELECT 
  id,
  TYPE,
  uExtractPart1(TYPE) as part1,
  uExtractPart2(TYPE) as part2,
  uExtractPart3(TYPE) as part3
FROM Article
ORDER BY 
  uExtractPart1(TYPE),
  uExtractPart2(TYPE),
  uExtractPart3(TYPE)