Shakespeare wrote:
> "Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht
> news:46af89eb$0$231$e4fe514c_at_news.xs4all.nl...
>> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
>> news:1185886692.520459_at_bubbleator.drizzle.com...
>>> wajim wrote:
>>>> Hi all,
>>>> I have a work on a table that I already created and populated with
>>>> some values, numeric and non-numeric(alpha-numeric), the qustion is
>>>> How can I sort all values from the column in numeric order??
>>>> This is the table I created:
>>>>
>>>> SQL> select * from test_numb;
>>>>
>>>> COLUMN_1
>>>> ----------
>>>> 1
>>>> 10
>>>> 3
>>>> 34
>>>> 7
>>>> 6
>>>> 43
>>>> 3-A
>>>> 17-B
>>>> 3-B
>>>>
>>>> 10 rows selected.
>>>>
>>>> So it concerns the values 3-A, 17-B and 3-B
>>>> I know there is a function ASCII(..) but don't see how to insert it in
>>>> a select tatement;
>>>> Thanks in advance.
>>> One way is to:
>>>
>>> ORDER BY TO_NUMBER(TRANSLATE(column_1, '0ABCDEFGHIJKLMNOPQRSTUVWXYZ-',
>>> '0'))
>>>
>>> also look at the "Non-Default Ordering" demo in Morgan's Library at
>>> www.psoug.org under ORDER BY for other situations.
>>> --
>>> Daniel A. Morgan
>>> University of Washington
>>> damorgan_at_x.washington.edu (replace x with u to respond)
>>> Puget Sound Oracle Users Group
>>> www.psoug.org
>> Looks like this would sort 17-B BELOW 170 (translates to 1700). Non
>> numeric characters should not be translated to 0 but be stripped.
>>
>> Shakespeare
>> (What's in a sort?)
>>
>
> I stand corrected, after reviewing this, it seems this DOES perform
> stripping! Confused this with a translate/replace function in a different
> language....
>
> Only problem unsolved here is to sort 3-B below 3-A, which can be done by
> order by <Daniels sort>, column_1
>
> Shakespeare
> (What's in a translation?)
Glad you saw that. Too bad I didn't see this post first.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jul 31 2007 - 17:40:43 CDT
