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
ldbartelsAsked:
Who is Participating?
 
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sathyagiriCommented:
Oops sorry that was a mistake didn't look at the whole data set
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.