Re: Help me about charactef function!!

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 1 Aug 2007 10:47:02 +0200
Message-ID: <46b0487e$0$244$e4fe514c@news.xs4all.nl>

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

> 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

Point taken, see some previous posts!

Shakespeare
(What's in a point?) Received on Wed Aug 01 2007 - 03:47:02 CDT