Shakespeare wrote:
> "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?)
Not unless you don't know how TRANSLATE works.
--
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:02 CDT
