Re: Sorting numbers on a VARCHAR2 field

From: David Fitzjarrell <oratune_at_aol.com>
Date: Mon, 06 Nov 2000 16:34:37 GMT
Message-ID: <8u6mit$f3u$1@nnrp1.deja.com>

In our last gripping episode oracle4444_at_my-deja.com wrote:
> Actually you can append any character, i think still it works. I
didn't
> play with all posssible characters, but i played with most of the
common
> characters.
> I can understand why it is working with regular characters. Because
> oralce compares the character in each position, and LPADed characters
> are always same.
> I didn't understand how "NULL" is working.
> Hope someone else can clarify the solution.
>
> In article <3a06988f$0$28900_at_diablo.uninet.ee>,
> "Ivar Stolfot" <Ivar.Stolfot_at_mail.ee> wrote:
> > <oracle4444_at_my-deja.com> wrote in message
> > news:8u57af$ag4$1_at_nnrp1.deja.com...
> > > Hi-
> > > Try following stmt. It might work.
> > > Assuming that column width is 10.......
> > > select column1 from table1 order by lpad(column1,10,null);
> >
> > It works but I can't understand why? LPad('1',10,NULL) returns NULL.
> >
> > I'm used the following solution for sorting character fields as
numbers:
> > select column1 from table1
> > order by Length(column1), column1
> >
> > It's probably little bit slower.
> >
> > Iff
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

NULL, as C and C++ programmers know, is a HEX 0 ('\0'), the lowest nonnegative numeric value. By padding the column width with what is essentially 0 values (at least as far as the ASCII collating sequence is concerned) you are performing two acts to enable proper sorting:

Each value is the maximum field length.
There are no positions not populated by values.

Since the padding values are all the same the ASCII sort will sort by the entire 10-character string. As values differ in each position the sort properly orders the data as though it were numeric:

\0\0\0\0\0\0\0\0\01

is different from

\0\0\0\0\0\0\0\010

and sorts properly:

         1
        10

...

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Nov 06 2000 - 10:34:37 CST