Re: Help me about charactef function!!


"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?)


.

Relevant Pages

  • Re: null bombing?
    ... is a sort of censorship out of Washington). ... Actually I could sort of believe that, but I doubt it's the spam doing it. ... Forgotten Futures - The Scientific Romance Role Playing Game ...
    (rec.arts.sf.fandom)
  • Re: TV Guide: Isaiah Washington out at "Greys Anatomy"
    ... I sort of suspected that he was told right after he shot off his mouth ... start establishing sincere ties with and understanding of the gay ... Washington to be fired all along. ... I am so done with plans. ...
    (rec.arts.tv)
  • Re: Do Disaster/Post-Apoc/End-o-the-World characters ever have families?
    ... been attending a conference in Washington, D.C., that day. ... sort of alternated between "I think he's dead" and "I can't ...
    (rec.arts.sf.written)
  • Re: News: Entombed microbes flourish again in lab.
    ... By RANDOLPH E. SCHMID, AP Science Writer Mon Aug 6, 5:04 PM ET ... WASHINGTON - Microorganisms locked in Antarctic ice for 100,000 years ... you gotta take blood samples from every guy who has come into ... what sort of safety precautions do labs that do this sort ...
    (talk.origins)
  • Re: Question on Insert Trigger
    ... some table or a package variable (any sort of "external to the row" data), can it still be written as a constraint? ... Presumably VPD cannot "ignore" input data, so it isn't applicable here I guess. ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.server)