Re: Help me about charactef function!!

"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?) Received on Tue Jul 31 2007 - 14:13:54 CDT