Re: SQL - Strange behavior

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/06/15
Message-ID: <961092371.12532.1.pluto.d4ee154e@news.demon.nl>#1/1

No strange behavior!
According to the ascii standard the numbers are on position 48 until 57 while the 'A' comes at position 65.
If you are using nls_sort = binary the select is exactly behaving as expected

A small demo follows

SQL> alter session set nls_sort=binary
  2 /

Session altered.

SQL> select * from nlssorting
  2 order by 1
  3 /

TEXTE

---

---

0001
0002
ZUABSCHLAG SQL> alter session set nls_sort=German
  2 /

Session altered.

SQL> select * from nlssorting order by 1   2 /

TEXTE

---

---

ZUABSCHLAG
0001
0002

Quod erat demonstrandum

Hth,

Sybrand Bakker, Oracle DBA

"Christian Hartmann" <c.hartmann_at_tmp-office.de> wrote in message news:8iaodg$4h4ne$1_at_fu-berlin.de...
> Hi there,
>
> I have very strange results with a table in which are "normal" Strings and
> Strings like 0001 included.
>
> Well, I did the following:
>
> Create table test (test_nr char(18))
>
> Values:
>
> Insert into test values ('ZUABSCHLAG')
> Insert into test values ('0001')
> Insert into test values ('0002')
>
> 1. Query
>
> Select test_nr from test order by test_nr
>
> Result:
> ZUABSCHLAG
> 0001
> 0002
>
> 2. Query
>
> Select test_nr from test where test_nr>'ZUABSCHLAG'
>
> Result:
> No rows
>
> Thanx for any help,
>
> Christian Hartmann
>
>
>
>
Received on Thu Jun 15 2000 - 00:00:00 CDT