• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1768
  • Last Modified:

Complex Mixed Alphanumeric Sorting in Oracle

I am trying to sort a list of street addresses (single column) in Oracle 10g. The data is very dirty. The order needs to look like:
1ST ST
2ND ST
3RD ST
...
10TH ST
14TH HALF ST
18TH 1/2 ST
...
ABELIA DR
AGAVE LN
0
ldbartels
Asked:
ldbartels
  • 8
  • 7
  • 4
  • +1
1 Solution
 
gatorvipCommented:
What does your actual data look like?
0
 
sathyagiriCommented:
Just a regular order by should produce your required output

select * from table order by addr_column
0
 
sathyagiriCommented:
Oops sorry that was a mistake didn't look at the whole data set
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ldbartelsAuthor Commented:
The data items listed in the original question are examples of the actual data. I tried to list, in the order I want it, providing an example of how different the street data is.
0
 
sathyagiriCommented:
Try this
select * from table
order by to_number(replace(column_name,replace(translate(column_name,'0123456789','##########'),'#',''),'')),column_name
0
 
ldbartelsAuthor Commented:
That does not work...ORA-01722: invalid number.  The following streets fail, as an example:
FM 1625 RD
FM 812 RD
OLD WEST 38TH ST

The patterns of characters and numbers vary in the street name
0
 
gatorvipCommented:
How about this:

select  address
from
(
select address,
  SUBSTR(addr,1,INSTR(address||' ',' ')) firstword
From <your_table>
)
order by (case
    when regexp_substr(firstword, '^[0-9]+') is null then firstword
    when regexp_substr(firstword, '^[0-9]+') is not null
      then lpad(regexp_substr(firstword, '^[0-9]+'), 15, '0')
    end)

the inner query extracts the first word from the address, then the sort by regex takes either the first number (padded to 15 digits with 0 - i assume you dont have any addresses that have 15 digits in them) or the first word
0
 
sathyagiriCommented:
Oh okie. The above solution will work only in cases where the number is at the beginning of the string
0
 
Mark GeerlingsDatabase AdministratorCommented:
It looks like you want a numeric sort *IF* the first few characters of the data are actually numeric, but otherwise an alphabetic sort *AND* you want the alphabetic sort to recognize "HALF" and "1/2" (for example) as logical equivalents.  Good luck!   I don't think you will find an existing built-in function or program in Oracle to do that!
0
 
ldbartelsAuthor Commented:
actually...I think gatorvip may have solved the problem...at least got me closer than I have been on my own. I need to do some testing of the results as the dataset is HUGE...but so far looks good! I will test before accept the solution...thanks a bunch!!!
0
 
ldbartelsAuthor Commented:
the solution is almost right. Some of the stricly character data items not sorted properly...for example:

AVENUE A
AVENUE I
AVENUE F
AVENUE G
AVENUE D
AVENUE B
...
and
...
BANCROFT WOODS DR
BANCROFT WOODS CV
0
 
gatorvipCommented:
add  the actual address to the order by clause

select  address
from
(
select address,
  SUBSTR(address,1,INSTR(address||' ',' ')) firstword
From <your_table>
)
order by (case
    when regexp_substr(firstword, '^[0-9]+') is null then firstword
    when regexp_substr(firstword, '^[0-9]+') is not null
      then lpad(regexp_substr(firstword, '^[0-9]+'), 15, '0')
    end), address
0
 
sathyagiriCommented:
Try this then.. same as gatorvip's except for address being added as second sort criteria
select  address
from
(
select address,
  SUBSTR(addr,1,INSTR(address||' ',' ')) firstword
From <your_table>
)
order by (case
    when regexp_substr(firstword, '^[0-9]+') is null then firstword
    when regexp_substr(firstword, '^[0-9]+') is not null
      then lpad(regexp_substr(firstword, '^[0-9]+'), 15, '0')
    end),address
0
 
ldbartelsAuthor Commented:
You rock gatorvip!!!
0
 
ldbartelsAuthor Commented:
Thank you both, gatorvip and sathyagiri. I appreciate both of your help.
0
 
sathyagiriCommented:
I think you accepted mine instead of gatorvip's. Please accept gatorvip's as his was the solution that worked
0
 
ldbartelsAuthor Commented:
I meant to accept gatorvip's solution but selected the wrong one. how do i fix that as I am new to the site and just joined today. Gatorvip solved the problem first.
0
 
sathyagiriCommented:
0
 
ldbartelsAuthor Commented:
posted a request. gatorvip, I promise I will fix this and accept your solution. Thanks again.
0
 
gatorvipCommented:
thanks, nice gesture!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now