Re: Help me about charactef function!!

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 1 Aug 2007 10:45:49 +0200
Message-ID: <46b04836$0$234$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1185921640.814115_at_bubbleator.drizzle.com...

> 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

Don't worry, I was too quick in my judgement!

Shakespeare Received on Wed Aug 01 2007 - 03:45:49 CDT