Numeric characters after alphabetic characters sorting varchar2 fields

[Max]

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

Example:
AAA
BBB
111
FFF
XXX
666
ZZZ

Sorted:
AAA
BBB
FFF
XXX
ZZZ
111
666

I think is right:
111
666
AAA
BBB
FFF
XXX
ZZZ

Thank you!

[Max]

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
> > XXX
> > 666
> > ZZZ
> >
> > Sorted:
> > AAA
> > BBB
> > FFF
> > XXX
> > ZZZ
> > 111
> > 666
> >
> > I think is right:
> > 111
> > 666
> > AAA
> > BBB
> > FFF
> > XXX
> > ZZZ
> >
> > Thank you!
> >
> >
> >

[Chuck]

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
>> > XXX
>> > 666
>> > ZZZ
>> >
>> > Sorted:
>> > AAA
>> > BBB
>> > FFF
>> > XXX
>> > ZZZ
>> > 111
>> > 666
>> >
>> > I think is right:
>> > 111
>> > 666
>> > AAA
>> > BBB
>> > FFF
>> > XXX
>> > ZZZ
>> >
>> > Thank you!
>> >
>> >
>> >

>
>

[Max]

> 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
> >> > XXX
> >> > 666
> >> > ZZZ
> >> >
> >> > Sorted:
> >> > AAA
> >> > BBB
> >> > FFF
> >> > XXX
> >> > ZZZ
> >> > 111
> >> > 666
> >> >
> >> > I think is right:
> >> > 111
> >> > 666
> >> > AAA
> >> > BBB
> >> > FFF
> >> > XXX
> >> > ZZZ
> >> >
> >> > Thank you!
> >> >
> >> >
> >> >

> >
> >

>