Re: Sort of numbers and letters

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 10 Feb 2004 06:47:49 -0800
Message-ID: <1076424420.465362@yasure>

Florian Albrecht wrote:

> Thank you Rene!
>
> A good hint!
>
>

>>Oracle Locale Builder might help you. I am not sure as I haven't tried
>>it. Find the details at:
>>
>>download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch12.htm#1008740

>
>
> It looks like this tool is only available for Oracle 9i, we are using
> 8i. But there is a description how the sort order can be changed with
> the Locale Builder. As it is only a GUI to set up some properties, I
> suppose there is also a way to get this done without this tool and may
> be it can be done in version 8i.
>
> I am still looking for a solution...
>
>
> ;-) Florian

One way is a variation on this:

SELECT address FROM (

    SELECT address, TO_NUMBER(TRIM(TRANSLATE(address,     0ABCDEFGHIJKLMNOPOQRSTUVWXYZabcdefghijklmnopqrstuvwxyz.,     ','0'))) SORTCOL
FROM test
ORDER BY SORTCOL);

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Feb 10 2004 - 08:47:49 CST