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_at_softhome.net> ha scritto nel messaggio news:Xns93B15C028DCAEchuckhsofthomenet_at_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_at_ideainformatica.org> wrote in news:be1ffo$6nib$1
> @ID-121437.news.dfncis.de:
>
> > NLS_LANGUAGE = ITALIAN
> > NLS_SORT = WEST_EUROPEAN
> >
> > "Chuck" <chuckh_at_softhome.net> ha scritto nel messaggio
> > news:Xns93AD69D93C44Fchuckhsofthomenet_at_130.133.1.4...
> >> What are your session's values for NLS_SORT and NLS_LANGUAGE?
> >>
> >> "Max" <massimo.panero_at_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!
> >> >
> >> >
> >> >
> >
> >
>
Received on Wed Jul 09 2003 - 05:25:22 CDT