Re: Help me about charactef function!!
- From: "Shakespeare" <whatsin@xxxxxxxxx>
- Date: Tue, 31 Jul 2007 21:13:54 +0200
"DA Morgan" <damorgan@xxxxxxxxx> schreef in bericht
news:1185886692.520459@xxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx (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?)
.
- Follow-Ups:
- Re: Help me about charactef function!!
- From: Shakespeare
- Re: Help me about charactef function!!
- References:
- Help me about charactef function!!
- From: wajim
- Re: Help me about charactef function!!
- From: DA Morgan
- Help me about charactef function!!
- Prev by Date: Re: Reports in OLTP system
- Next by Date: Re: Help me about charactef function!!
- Previous by thread: Re: Help me about charactef function!!
- Next by thread: Re: Help me about charactef function!!
- Index(es):
Relevant Pages
|