Oracle / SQL ORDER BY语句出现问题

i have the following content inside an varchar2 column:

我在varchar2列中有以下内容：

   10.1.2.3
   10.2.3.4
   8.3.4.1
   8.3.2.1
   4.2.1.3
   4.3.2.1
   9.3.1.2

When i query the database i need an result ordered:

当我查询数据库时，我需要一个有序的结果：

4....
8....
9....
10...

the NLS_SORT parameter is set to german, an simple "order by COLUMN DESC/ASC" is not working like excepted. It returns

NLS_SORT参数设置为德语，一个简单的“由COLUMN DESC / ASC命令”不能像例外一样工作。它回来了

10.....
8......
9......

any suggestions?

有什么建议么？

7 个解决方案

#1

Assuming it's an IP address

假设它是一个IP地址

SELECT col
  FROM table
 ORDER BY 
(regexp_substr(col, '[^.]+', 1, 1) * 256  * 256  * 256 ) + (regexp_substr(col, '[^.]+', 1, 2) * 256 * 256) + (regexp_substr(col, '[^.]+', 1, 3) * 256 )+ regexp_substr(col, '[^.]+', 1, 4)

#2

@RobVanWijk makes a pertinent comment:

@RobVanWijk发表了相关评论：

you could argue that those should be stored as four numeric columns instead of 1 string.

你可以争辩说那些应该存储为四个数字列而不是1个字符串。

This is a classic case where it would be cool if we could define data domains in our schemas. Oracle doesn't support this, but to be fair nor do any of the other DBMS vendors. Still, we can employ User-defined types to build complex datatypes with attached behaviour. It's just a shame that the UDT syntax is so clunky.

这是一个经典案例，如果我们可以在我们的模式中定义数据域，那将会很酷。 Oracle不支持这一点，但公平也不支持任何其他DBMS供应商。尽管如此，我们仍然可以使用用户定义的类型来构建具有附加行为的复杂数据类型。令人遗憾的是，UDT语法非常笨重。

Anyway, Rob's comment has reminded me that I knocked up a Proof of Concept using this very domain a while back. I am posting it not as a serious solution, but as an indicator of how neat things could be....

无论如何，Rob的评论提醒我，我在一段时间内使用这个领域敲了一个概念验证。我发布它不是一个严肃的解决方案，而是作为一个多么好的事情的指标....

The type specification ...

型号规格......

create or replace type ip_address_t as object
    (octet1 number(3,0) 
     , octet2 number(3,0) 
     , octet3 number(3,0) 
     , octet4 number(3,0) 
     , constructor function ip_address_t 
            (octet1 number, octet2 number, octet3 number, octet4 number)
                        return self as result
     , member function to_string 
                        return varchar2
     , member function to_padded_string 
                        return varchar2
     , map member function sort_order return number)
/

... and body ...

... 与身体 ...

create or replace type body ip_address_t as 

    constructor function ip_address_t 
         (octet1 number, octet2 number, octet3 number, octet4 number)
                        return self as result
    is
    begin
        if ( octet1 is null or octet2 is null or octet3 is null or octet4 is null )
        then
            raise INVALID_NUMBER;
        else
            self.octet1 := octet1;
            self.octet2 := octet2;
            self.octet3 := octet3;
            self.octet4 := octet4;
        end if;
        return;
    end ip_address_t;

    member function to_string return varchar2
    is
    begin    
        return trim(to_char(self.octet1))||'.'||
               trim(to_char(self.octet2))||'.'||
               trim(to_char(self.octet3))||'.'||
               trim(to_char(self.octet4));
    end to_string;

    member function to_padded_string  return varchar2
    is
    begin    
        return lpad(trim(to_char(self.octet1)),3,'0')||'.'||
               lpad(trim(to_char(self.octet2)),3,'0')||'.'||
               lpad(trim(to_char(self.octet3)),3,'0')||'.'||
               lpad(trim(to_char(self.octet4)),3,'0');
    end to_padded_string;

    map member function sort_order return number
    is
    begin    
        return to_number(
                       lpad(trim(to_char(self.octet1)),3,'0')||
                       lpad(trim(to_char(self.octet2)),3,'0')||
                       lpad(trim(to_char(self.octet3)),3,'0')||
                       lpad(trim(to_char(self.octet4)),3,'0')
              );
     end sort_order;

end;
/

I will use this type to define a column in a test table which I will populate with some test data.

我将使用此类型在测试表中定义一个列，我将填充一些测试数据。

SQL> create table t23 (id number, domnain_name varchar2(128), ip_address ip_address_t)
  2  /

Table created.

SQL> insert into t23 values (1000, 'http://www.example.com', ip_address_t(8,1,3,0))
  2  /

1 row created.

SQL> insert into t23 values (800, 'http://www.example1.com', ip_address_t(9,1,2,0))
  2  /

1 row created.

SQL> insert into t23 values (1100, 'http://www.example2.com', ip_address_t(10,1,2,0))
  2  /

1 row created.

SQL> insert into t23 values (1103, 'http://www.example3.com', ip_address_t(10,1,25,0))
  2  /

1 row created.

SQL> insert into t23 values (1102, 'http://www.example4.com', ip_address_t(1,11,25,0))
  2  /

1 row created.

SQL> insert into t23 values (1101, 'http://www.example5.com', ip_address_t(11,1,25,0))
  2  /

1 row created.

SQL>

Always remember: when referencing the attributes or methods of a UDT column we have to use a table alias:

永远记住：在引用UDT列的属性或方法时，我们必须使用表别名：

SQL> select t.id
  2         , t.ip_address.to_string() as ip_address
  3  from t23 t
  4  order by t.ip_address.sort_order()
  5  /

        ID IP_ADDRESS
---------- ---------------
      1102 1.11.25.0
      1000 8.1.3.0
       800 9.1.2.0
      1100 10.1.2.0
      1103 10.1.25.0
      1101 11.1.25.0

SQL>

#3

Create a cursor with which you will loop into;
创建一个用于循环的游标;
Use a FOR..LOOP while using a [TABLE_NAME]%ROWTYPE as information data container;
使用[TABLE_NAME]％ROWTYPE作为信息数据容器时使用FOR..LOOP;
Split your string and cast to NUMBER the first string obtained;
拆分你的字符串并转换为NUMBER获得的第一个字符串;
Do the same for the following strings gotten from splitting over and over again for each of the numbers;
对于每个数字一遍又一遍地分割以下字符串，同样如此;
Insert the sorted result into a temporary table and select the result from it.
将排序后的结果插入临时表并从中选择结果。

Otherwise:

除此以外：

You also could add a new column to sort records with to your data table, which according to me is the better approach, if doable into your situation.

您还可以添加一个新列来对您的数据表中的记录进行排序，根据我的说法，这是更好的方法，如果可以适应您的情况。

#4

That is the proper order. 1 comes before 8, which comes before 9. Since those are not "numbers", they are text, you probably have to either convert the first part to a number, or zero fill them when sorting.

这是正确的顺序。 1出现在8之前，它出现在9之前。由于它们不是“数字”，它们是文本，您可能必须将第一部分转换为数字，或者在排序时将它们填充为零。

For example, if you make each segment 3 digits wide, say 008.002.004.001 then you can sort alphabetically and it will work. You can display it however you want, but have to sort it differently.

例如，如果您将每个段宽3位数，例如008.002.004.001，那么您可以按字母顺序排序，它将起作用。您可以根据需要显示它，但必须以不同方式对其进行排序。

#5

10 in a string is different from 10 in a number.

字符串中的10与数字中的10不同。

Strings will sort as follows

字符串将排序如下

10
4
8

Numbers will sort like this

数字会像这样排序

4
8
10

You will have to take part of the string (before the first period), convert it to a number and then sort it. Look up SUBSTR and INSTR functions for this. Then look up for a function to convert from string to integer.

您将不得不接受部分字符串（在第一个句点之前），将其转换为数字然后对其进行排序。为此查找SUBSTR和INSTR函数。然后查找一个从字符串转换为整数的函数。

You will have to do this for each Octet (I only say that because the numbers you have shown look like an IP address)

您必须为每个Octet执行此操作（我只是说，因为您显示的数字看起来像一个IP地址）

So basically you will need to separate out the data into four integer columns and then sort by them (or, just do the separation in the ORDER BY Clause).

所以基本上你需要将数据分成四个整数列，然后按它们排序（或者，只需在ORDER BY子句中进行分离）。

#6

Use regular expressions:

使用正则表达式：

order by lpad(ltrim(regexp_substr(COLUMN,'(.?[^.])|([^.];?)',1,1),'.'),3,'0') , lpad(ltrim(regexp_substr(COLUMN,'(.?[^.])|([^.];?)',1,2) ,'.'),3,'0') , lpad(ltrim(regexp_substr(COLUMN,'(.?[^.])|([^.];?)',1,3) ,'.'),3,'0') , lpad(ltrim(regexp_substr(COLUMN,'(.?[^.])|([^.];?)',1,4) ,'.'),3,'0')

按lpad排序（ltrim（regexp_substr（COLUMN，'（。？[^。]）|（[^。];？）'，1,1），'。'），3，'0'），lpad（ltrim）（regexp_substr（COLUMN，'（。？[^。]）|（[^。];？）'，1,2），'。'），3，'0'），lpad（ltrim（regexp_substr（COLUMN， '（。？[^。]）|（[^。];？）'，1,3），'。'），3，'0'），lpad（ltrim（regexp_substr（COLUMN，'（。？[ ^。]）|（[^。];？）'，1,4），'。'），3，'0'）

#7

-1

it should be

它应该是

order by COLUMN

By default it is ascending only.

默认情况下，它仅提升。

#1