如何在oracle 10g中根据住址进行排序？

I want to query the Oracle 10g database and get result on the basis of residential address in ascending or descending order. So the problem is building addresses are at time in the form of 110C or 200D, so according to sql "order by" asc or desc, I get the result as 200D before 20. for eg, if adresses are 10 110 112 200D 232 95 20 100A 1050 944

我想查询Oracle 10g数据库,并根据住宅地址按升序或降序获得结果。所以问题是建立地址的时间是110C或200D,所以根据sql“order by”asc或desc,我得到结果为20D之前的20D。例如,如果地址是10 110 112 200D 232 95 20 100A 1050 944

In ascending order it will result in:-

按升序排列将导致: -

 10 100 100A 1050 110 112 20 200D 232 944 95

The problem is as the adresses have characters in it, i can't consider them to be as integers or number, they have to considered as String.

问题是因为地址中有字符,我不能认为它们是整数或数字,它们必须被视为字符串。

5 个解决方案

#1

Use regular expressions:

使用正则表达式:

Warning! Potential non-working code ahead. I do not have an Oracle instance to test it against at the moment.

警告!未来潜在的非工作代码。我目前没有Oracle实例来测试它。

SELECT YourAddress
FROM YourTable
order by TO_NUMBER(REGEXP_SUBSTR(AddressColumn, '^[0-9]+'));

REGEXP_SUBSTR will find the number's substring which is supposed to start the address, convert it to a real number and order by it.

REGEXP_SUBSTR将找到应该启动地址的数字的子字符串,将其转换为实数并按顺序排序。

Caveat: you will have to improve the query to handle cases where a number will not be present. However, this answer can get you very well started.

警告:您将不得不改进查询以处理数字不存在的情况。但是,这个答案可以让你很好地开始。

When in need to fine-tune the query, here are the resources you should use:

当需要微调查询时,您应该使用以下资源:

And, yes, REGEXP_SUBSTR is available in Oracle 10g.

并且,是的,Oracle 10g中提供了REGEXP_SUBSTR。

#2

The regular expression based solutions are more elegant. But assuming you want to first sort on the numeric component using a numeric sort and then sort on the character component using a character sort, you can also use the TRANSLATE function.

基于正则表达式的解决方案更加优雅。但是假设您要首先使用数字排序对数字组件进行排序,然后使用字符排序对字符组件进行排序,您还可以使用TRANSLATE函数。

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select '10' addr from dual union all
  3    select '100' from dual union all
  4    select '100A' from dual union all
  5    select '1050' from dual union all
  6    select '110' from dual union all
  7    select '200D' from dual union all
  8    select '20' from dual
  9  )
 10  select addr,
 11         to_number( translate( addr,
 12                               '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 13                               '1234567890' ) ) addr_num,
 14         translate( addr,
 15                    'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',
 16                    'ABCDEFGHIJKLMNOPQRSTUVWXYZ') addr_str
 17    from x
 18   order by to_number( translate( addr,
 19                                  '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 20                                  '1234567890' ) ),
 21            translate( addr,
 22                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',
 23*                      'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
SQL> /

ADDR   ADDR_NUM ADDR_STR
---- ---------- ----------------
10           10
20           20
100A        100 A
100         100
110         110
200D        200 D
1050       1050

7 rows selected.

#3

The only way to accomplish this is to parse the street numbers into numeric and non-numeric parts, and store them in separate columns so you can index (or order by) on the numeric part first (as a number) and then the non-numeric part. It may be possible to write an expression that does this, but then you lose any indexing (if that's important).

实现此目的的唯一方法是将街道号码解析为数字和非数字部分,并将它们存储在单独的列中,以便您可以首先在数字部分上索引(或排序)(作为数字)然后非数字部分。有可能编写一个执行此操作的表达式,但随后丢失任何索引(如果这很重要)。

In Oracle 11 there's a REGEXP_SUBSTR function that could be used to accomplish this, but I'm not sure it exists in Oracle 10g.

在Oracle 11中有一个可用于实现此目的的REGEXP_SUBSTR函数,但我不确定它是否存在于Oracle 10g中。

#4

You have two basic options that I am aware of:
1. Force the field to a consistent format
2. Separate the numeric and non-numeric portions out

您有两个我知道的基本选项:1。强制字段采用一致的格式2.将数字和非数字部分分开

For option one, you would change '10' to be '00010-', and '100A' to be '00100A', etc. They will then naturally order themselves.

对于选项一,您可以将'10'更改为'00010-',将'100A'更改为'00100A'等。然后他们将自然地自行订购。

For option two, you would change '10' to be (10,''), and '100A' to be (100,'A'), etc. Then order by the two portions (numeric and non-numeric) separately.

对于选项二,您可以将'10'更改为(10,''),将'100A'更改为(100,'A')等。然后分别按两个部分(数字和非数字)排序。

Either way, you need to know (or tell us) a little bit more about the definitive characteristics of the data. (Is the pattern always [numeric][alpha], is the [alpha] portion always 1 character long, Do you ever have building Names, etc, etc?)

无论哪种方式,您都需要了解(或告诉我们)更多关于数据的确定性特征。 (模式总是[数字]α,是α部分总是1个字符长,你有建筑名称等等吗?)

#5

I have no experience with oracle but generally with SQL something like that could help:

我没有使用oracle的经验,但通常使用SQL可以帮助:

ORDER BY CAST(house AS integer), house

So basically you order by integer part of the field first (CAST should disregard all trailing non numerical chars), and if they are the same it should compare them as a string so 100A would be before 100b

所以基本上你首先按字段的整数部分排序(CAST应该忽略所有尾随的非数字字符),如果它们是相同的,它应该将它们作为字符串进行比较,所以100A将在100b之前

Apparently this will not work in Oracle. See comments

显然这在Oracle中不起作用。看评论

#1