Re: Help me about charactef function!!

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