如何使用没有特定格式的数字和字母对 varchar 进行排序? [英] How do I Sort a varchar with numbers and letters without a specific format?

问题描述

我有一个名为 MR 的列，它是一个 varchar.当我使用 ORDER BY 运行查询时，它似乎没有正确排序.

I have a column named MR which is a varchar. When I run a query with an ORDER BY it doesn't seem to be ordered correctly.

select MR, LName, FName 
from users
order by MR

结果:

MR        | LNAME | FNAME
----------+-------+-------
1234-234  | HEN   | LO
2343MA2   | SY    | JACK
MR20001   | LINA  | MARY
MR200011  | TEST  | CASE
MR20002   | KO    | MIKE

为什么 MR200011 在 MR20002 之前显示?关于如何正确排序的任何想法家伙?MR的格式不固定.

Why does MR200011 show before MR20002? Any Idea guys on how I can properly sort this? The format of MR is not fixed.

推荐答案

您是按字符串排序，而不是按数字的值排序.位置 7 的字符是被比较的差异:

You are sorting by string, not by the value of the number. The character in position 7 is the difference that's being compared:

MR200011 
MR20002 
      ^

因为'2' > '1'，这就是你最终得到的顺序.永远不会比较第 8 个字符，因为基于字符的排序顺序不依赖于它.

And because '2' > '1', this is the order you end up with. The 8th character is never compared, because the character-based sort order doesn't depend on it.

要修复"此问题，请创建一个存储函数，该函数接受您的 varchar 值，并返回一个新的排序字符串"，该字符串将数字组件填充为固定长度.

To 'fix' this issue, create a stored function which takes your varchar value, and returns a new 'sort string' which pads the numeric components to a fixed length.

例如

MR20002  -> MR0020002
MR200011 -> MR0200011

但更重要的是，如果您有两个数字块，它们不会损坏:

but more importantly, if you have two blocks of numbers, they don't become corrupted:

A1234-234  -> A000000001234-000000000234
A1234-5123 -> A000000001234-000000005123

以下函数在 sql-server 上执行此转换 - 您必须为 mysql 调整此函数:

The following function performs this transformation on sql-server - you'd have to adapt this function for mysql:

create function dbo.get_numeric_sort_key(@value varchar(100)) 
    returns varchar(200)
as
begin
   declare @pad_characters varchar(12)
   declare @numeric_block varchar(12)
   declare @output varchar(200)
   set @pad_characters = '000000000000'
   set @output = ''
   set @numeric_block = ''

   declare @idx int
   declare @len int
   declare @char char(1)
   set @idx = 1
   set @len = len(@value)
   while @idx <= @len
   begin
     set @char = SUBSTRING(@value, @idx, 1)
     if @char in ('0','1','2','3','4','5','6','7','8','9') 
     begin
        set @numeric_block = @numeric_block + @char
     end
     else
     begin
        if (@numeric_block <> '')
        begin
          set @output = @output + right(@pad_characters + @numeric_block, 12)
          set @numeric_block = ''
        end
        set @output = @output + @char
     end
     set @idx = @idx + 1
   end

   if (@numeric_block <> '')
     set @output = @output + right(@pad_characters + @numeric_block, 12)

   return @output
end

然后更改您的 order by 子句以使用新函数:

Then change your order by clause to use the new function:

select MR, LName, FName 
from users 
order by dbo.get_numeric_sort_key(MR)

如果您有大量数据，值得在表定义的末尾添加一个计算字段(由该函数填充)，这样您就不必每次运行此查询时都进行扫描.

If you have a large amount of data, it would be worth adding a calculated field to the end of your table definition (populated by this function) so that you don't have to do a scan every time you run this query.

这篇关于如何使用没有特定格式的数字和字母对 varchar 进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！