How to get sorted result based on residential address in oracle 10g?

Question

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

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.

Answer 1

Use regular expressions:

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

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.

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:

• http://psoug.org/reference/regexp.html
• http://psoug.org/snippet/Regular-Expressions--Regexp-Cheat-Sheet_856.htm
• http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions116.htm

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

Answer 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.

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.