Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help me about charactef function!!

Re: Help me about charactef function!!

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 31 Jul 2007 22:14:15 +0200
Message-ID: <46af9811$0$227$e4fe514c@news.xs4all.nl>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US