MySql varchar ordering

Question

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.

Seems like you're talking about natural sorting. http://stackoverflow.com/questions/153633/natural-sort-in-mysql — Ian, Jan 29 '13 at 14:09
@bonCodingo No, there are lots of other records like number-letters-number, letters-number-letters-number-letters and so on. Your suggested sorting method worked good only in this case with `mova`. — Gereby, Jan 29 '13 at 14:33

score 2 · Accepted Answer · answered Jan 29 '13 at 22:29

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`)

Thanks. It worked like a charm! Of course, performance ain't that well as before with lots of records, but I'll try to cope with that on my own now. — Gereby, Jan 31 '13 at 09:21

score 1 · Answer 2 · answered Jan 29 '13 at 14:05

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 |

MySql varchar ordering

2 Answers2

Linked