Mysql varchar排序 [英] MySql varchar ordering

问题描述

关于另一个完全相同的问题，我还有另一个问题.我阅读了很多论坛和有关该主题的内容，但是找不到答案.我正在使用LPAD，因为我的需求可以得到最接近的结果，但是仍然不是我想要的.

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

现在的记录如下:

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

但是我需要按此顺序:

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

那么，我该如何实现呢?

我必须注意，该表中还有其他记录，例如:

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.

推荐答案

我的想法是将您的列转换为固定长度的字符串，并以字母数字字符和数字按块对齐，例如

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 ;

(此处每个块必须限制为10个字符).

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

您可以通过以下方式订购:

You can then order by:

ORDER BY strformat(`my_value`)

这篇关于Mysql varchar排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！