Re: [Maybe OT]Sorting VARCHAR with letters and numbers

Daniel Cloutier wrote:

> Hello everyone!
>
>
> I have a slight problem which I donīt seem to understand why it is not working.
> I have a table which contains on column of type VARCHAR2. This column is filled
> with ID 'strings'. These strings are mixed, meaning some are numbers only and
> others are alphanumeric values or both (e.g. '123456', 'A123456' or 'ABCDEFG')
>
>
> Now, I needed to fill a listbox which shows the 20 successors and predecessors
> of a selected value. For the predecessors I used something like this:
>
>
> SELECT IDNUMBER FROM (SELECT IDNUMBER FROM MYTABLE WHERE FZNUM <='SELECTEDID'
> ORDER BY IDNUMBER DESC) WHERE ROWNUM<=20
>
>
> This seems to work and the result looks ok.
>
>
> Then for successors I simply did this:
>
>
> SELECT IDNUMBER FROM MYTABLE WHERE IDNUMER >='SELECTEDID' AND ROWNUM<=20;
>
>
> The strange thing is, that this works, when I type this string into SQL-Plus and
> execute it. As soon, as I use the Java programm, it does not work anymore and I
> get completely wrong unsorted results. Is there something I miss here or do I
> have to consider something in sorting thos type of data generally?
>
>
> I īm not sure, if this is a Java problem or an Oracle one. So this is why I
> marked it 'OT'.
>
>
> Any hint would be verry appreciated.
>
>
> Thanks!
> Joerg
>
>
>
>
>

Joerg, you are in fact sorting varchar2 columns, idnumber (varchar2) >= | < other_idnumber (varchar2). If your nls_sort is set to binary, you sort on the ascii values. To get that right you can lpad the ID to some fixed length to get the sorting right. You might have to set nls_sort to another value ie. nls_sort = german, if your application calls for it. But note that index   lookup (are sorted) as replacement for sorting varchar's wont work anymore.
But You might still be stuck with problems as lists like u1 < u10 < u2 < u20 < u3 < u30...
/Svend Jensen Received on Thu Jan 31 2002 - 14:12:18 CST