Numeric characters after alphabetic characters sorting varchar2 fields

From: Max <massimo.panero_at_ideainformatica.org>
Date: Tue, 2 Sep 2003 11:09:51 +0200
Message-ID: <bj1mov$eau5m$1@ID-121437.news.uni-berlin.de>

I have a question already submitted 2 months ago but never resolved.

Ihave to place numeric characters before alphabetic caracters sorting varchar2 fields.

Example:

Unsorted: [ AAA, bbb, 111, FFF, èèè, xxx, 666, ZZZ ]

   Sorted:       [ AAA, bbb, èèè, FFF, xxx, ZZZ, 111, 666 ]
   I need:        [ 111, 666, AAA, bbb, èèè, FFF, xxx, ZZZ ]

I use oracle parameters:
NLS_LANGUAGE = ITALIAN
NLS_SORT = WEST_EUROPEAN I can't use NLS_SORT = BINARY because I lose the "intelligent" sort that work case insensitive and with special characters.

Example:

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 need  =========================>  ( 1, 2, 3, A, a, à, C, c, E, e, è, X,

x )

Thanks a lot! Received on Tue Sep 02 2003 - 04:09:51 CDT