Professional Web Applications Themes
  1. #1

    Default Numeric characters after alphabetic characters sorting varchar2 fields

    Hi!
    Why numeric characters are placed after alphabetic characters sorting
    varchar2 fields?

    Example:
    AAA
    BBB
    111
    FFF

    666
    ZZZ

    Sorted:
    AAA
    BBB
    FFF

    ZZZ
    111
    666

    I think is right:
    111
    666
    AAA
    BBB
    FFF

    ZZZ

    Thank you!


    Max Guest

  2. #2

    Default Re: Numeric characters after alphabetic characters sorting varchar2 fields

    NLS_LANGUAGE = ITALIAN
    NLS_SORT = WEST_EUROPEAN

    "Chuck" <chuckh@softhome.net> ha scritto nel messaggio
    news:Xns93AD69D93C44Fchuckhsofthomenet@130.133.1.4 ...
    > What are your session's values for NLS_SORT and NLS_LANGUAGE?
    >
    > "Max" <massimo.panero@ideainformatica.org> wrote in news:be1drf$5fsm$1
    > @ID-121437.news.dfncis.de:
    >
    > > Hi!
    > > Why numeric characters are placed after alphabetic characters sorting
    > > varchar2 fields?
    > >
    > > Example:
    > > AAA
    > > BBB
    > > 111
    > > FFF
    > >
    > > 666
    > > ZZZ
    > >
    > > Sorted:
    > > AAA
    > > BBB
    > > FFF
    > >
    > > ZZZ
    > > 111
    > > 666
    > >
    > > I think is right:
    > > 111
    > > 666
    > > AAA
    > > BBB
    > > FFF
    > >
    > > ZZZ
    > >
    > > Thank you!
    > >
    > >
    > >

    Max Guest

  3. #3

    Default Re: Numeric characters after alphabetic characters sorting varchar2 fields

    There's you're answer. Under Western European collating sequence, numbers
    come after alpha. Try this to prove it.

    create table mytable (x varchar2(10));
    insert into mytable values (0);
    insert into mytable values ('a');
    commit;

    SELECT x FROM mytable ORDER BY NLSSORT(x,'NLS_SORT=BINARY');
    X
    ----------
    0
    a
    2 rows selected


    SELECT x FROM mytable ORDER BY NLSSORT(x,'NLS_SORT=WEST_EUROPEAN');
    X
    ----------
    a
    0
    2 rows selected


    drop table mytable;
    --
    Chuck

    "Max" <massimo.panero@ideainformatica.org> wrote in news:be1ffo$6nib$1
    @ID-121437.news.dfncis.de:
    > NLS_LANGUAGE = ITALIAN
    > NLS_SORT = WEST_EUROPEAN
    >
    > "Chuck" <chuckh@softhome.net> ha scritto nel messaggio
    > news:Xns93AD69D93C44Fchuckhsofthomenet@130.133.1.4 ...
    >> What are your session's values for NLS_SORT and NLS_LANGUAGE?
    >>
    >> "Max" <massimo.panero@ideainformatica.org> wrote in news:be1drf$5fsm$1
    >> @ID-121437.news.dfncis.de:
    >>
    >> > Hi!
    >> > Why numeric characters are placed after alphabetic characters
    sorting
    >> > varchar2 fields?
    >> >
    >> > Example:
    >> > AAA
    >> > BBB
    >> > 111
    >> > FFF
    >> >
    >> > 666
    >> > ZZZ
    >> >
    >> > Sorted:
    >> > AAA
    >> > BBB
    >> > FFF
    >> >
    >> > ZZZ
    >> > 111
    >> > 666
    >> >
    >> > I think is right:
    >> > 111
    >> > 666
    >> > AAA
    >> > BBB
    >> > FFF
    >> >
    >> > ZZZ
    >> >
    >> > Thank you!
    >> >
    >> >
    >> >
    >
    >
    Chuck Guest

  4. #4

    Default Re: Numeric characters after alphabetic characters sorting varchar2 fields

    > SELECT x FROM mytable ORDER BY NLSSORT(x,'NLS_SORT=BINARY');

    This apparently resolve my problem but I lose the "intelligent" sort that
    work case insensitive and with special characters:

    NLS_SORT = WEST_EUROPEAN ====> ( A, a, , C, c, E, e, , X, x, 1, 2, 3 )
    NLS_SORT = BINARY ============> ( 1, 2, 3, A, C, E, X, a, c, e, x, , )

    I only want to put number before alpha characters.


    "Chuck" <chuckh@softhome.net> ha scritto nel messaggio
    news:Xns93B15C028DCAEchuckhsofthomenet@130.133.1.4 ...
    > There's you're answer. Under Western European collating sequence, numbers
    > come after alpha. Try this to prove it.
    >
    > create table mytable (x varchar2(10));
    > insert into mytable values (0);
    > insert into mytable values ('a');
    > commit;
    >
    > SELECT x FROM mytable ORDER BY NLSSORT(x,'NLS_SORT=BINARY');
    > X
    > ----------
    > 0
    > a
    > 2 rows selected
    >
    >
    > SELECT x FROM mytable ORDER BY NLSSORT(x,'NLS_SORT=WEST_EUROPEAN');
    > X
    > ----------
    > a
    > 0
    > 2 rows selected
    >
    >
    > drop table mytable;
    > --
    > Chuck
    >
    > "Max" <massimo.panero@ideainformatica.org> wrote in news:be1ffo$6nib$1
    > @ID-121437.news.dfncis.de:
    >
    > > NLS_LANGUAGE = ITALIAN
    > > NLS_SORT = WEST_EUROPEAN
    > >
    > > "Chuck" <chuckh@softhome.net> ha scritto nel messaggio
    > > news:Xns93AD69D93C44Fchuckhsofthomenet@130.133.1.4 ...
    > >> What are your session's values for NLS_SORT and NLS_LANGUAGE?
    > >>
    > >> "Max" <massimo.panero@ideainformatica.org> wrote in news:be1drf$5fsm$1
    > >> @ID-121437.news.dfncis.de:
    > >>
    > >> > Hi!
    > >> > Why numeric characters are placed after alphabetic characters
    > sorting
    > >> > varchar2 fields?
    > >> >
    > >> > Example:
    > >> > AAA
    > >> > BBB
    > >> > 111
    > >> > FFF
    > >> >
    > >> > 666
    > >> > ZZZ
    > >> >
    > >> > Sorted:
    > >> > AAA
    > >> > BBB
    > >> > FFF
    > >> >
    > >> > ZZZ
    > >> > 111
    > >> > 666
    > >> >
    > >> > I think is right:
    > >> > 111
    > >> > 666
    > >> > AAA
    > >> > BBB
    > >> > FFF
    > >> >
    > >> > ZZZ
    > >> >
    > >> > Thank you!
    > >> >
    > >> >
    > >> >
    > >
    > >
    >

    Max Guest

Similar Threads

  1. HTML form fields and special characters
    By Alen in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: May 31st, 09:23 PM
  2. Do Database fields have a numeric equavilant?
    By technoplonker in forum ASP Database
    Replies: 1
    Last Post: January 13th, 03:40 PM
  3. DataGrid-DataView-XML-Sorting strings as numeric
    By Joe Rattz in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: November 20th, 09:51 PM
  4. Replies: 1
    Last Post: August 12th, 03:28 PM
  5. ViewState can't handle numeric fields?
    By Boban Dragojlovic in forum ASP.NET General
    Replies: 0
    Last Post: June 24th, 03:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139