MySql varchar ordering

问题:

I have another question about quite same problem as others. I read lots of forums and stuff about that, but can't find an answer. I'm using LPAD, because I've got a closest results for my needs, but it's still not as I want.

ORDER BY LPAD(`my_value`, '500', '0') ASC

Records looks like this now:

Mova 16
Mova 110
Mova 125
Mova 140
Mova 180
Mova 160N

But I need them in this order:

Mova 16
Mova 110
Mova 125
Mova 140
Mova 160N
Mova 180

So, how can I achieve that?

I must note, that there is kind of other records in this table, like:

"GIACOMINI" R780 DN15 v/2 kampinis
"GIACOMINI" R780 DN20 v/3 kampinis
"GIACOMINI" R780 DN25 v/1 kampinis

and so much more... Those should fit in this context, too.

回答1:

My idea is to transform your column into a fixed length string, with alphanumeric characters and digits aligned in blocks, e.g.

Input string                       | aligned string
---------------------------------------------------------------------------------------
Mova 16                            | MOVA......0000000016
Mova 110                           | MOVA......0000000110
Mova 180                           | MOVA......0000000180
Mova 160N                          | MOVA......0000000160N.........
"GIACOMINI" R780 DN15 v/2 kampinis | GIACOMINI.R.........0000000780DN........000..etc.
"GIACOMINI" R780 DN20 v/3 kampinis | GIACOMINI.R.........0000000780DN........000..etc.

and then we can sort the rows using the aligned strings, but to obtain to result we need to create a custom function:

DROP FUNCTION IF EXISTS strformat;
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER |
CREATE FUNCTION strformat(str VARCHAR(128))
RETURNS VARCHAR(128)
BEGIN
  DECLARE i INT;
  DECLARE last INT;
  DECLARE curr INT;
  DECLARE res VARCHAR(128);
  DECLARE block VARCHAR(10);

  SET res='';
  SET block='';
  SET i=1;

  IF LENGTH(str) = 0 THEN
    RETURN NULL;
  END IF;

  WHILE i <= LENGTH(str) DO
    SET curr=IF(MID(str, i, 1) RLIKE '[A-Z,a-z]', 1,
                  IF(MID(str, i, 1) RLIKE '[0-9]', 2, 0));

    IF (block='') OR (last=curr) THEN
      SET block = CONCAT(block,
                    IF((curr=1) OR (curr=2), MID(str, i, 1), ''));
    ELSE
      IF last=2 THEN
        SET res = CONCAT(res,
                    REPEAT('0', 10 - LENGTH(block)), block);
      ELSE
        SET res = CONCAT(res,
                    block, REPEAT(' ', 10 - LENGTH(block)));
      END IF;
      SET block = IF((curr=1) OR (curr=2), MID(str, i, 1), '');
    END IF;

    SET last=curr;
    SET i = i + 1;
  END WHILE;

  IF curr=1 THEN
    SET res = CONCAT(res, block, REPEAT(' ', 10 - LENGTH(block)));
  ELSEIF curr=2 THEN
    SET res = CONCAT(res, REPEAT('0', 10 - LENGTH(block)), block);
  END IF;

  RETURN UCASE(res);
END;
|
DELIMITER ;

(here each block has to be limited to 10 characters).

You can then order by:

ORDER BY strformat(`my_value`)

回答2:

How about this?

SELECT   *
FROM     yourtable
ORDER BY CAST(SUBSTRING(yourcolumn,LOCATE(' ',yourcolumn)+1) AS SIGNED) ASC;

• SQLFIDDLE DEMO

Results:

| YOURCOLUMN |
--------------
|    Mova 16 |
|   Mova 110 |
|   Mova 125 |
|   Mova 140 |
|  Mova 160N |
|   Mova 180 |