Try playing around with the translate function. See if you can convert
the numeric characters to something lower in the collating sequence. That
would force them to come out first.
"Max" <massimo.panero_at_ideainformatica.org> wrote in
news:begqhe$4imos$1_at_ID-121437.news.dfncis.de:
>> 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 Fri Jul 11 2003 - 08:11:52 CDT
