Oracle sort order and greater than operator are not consistent with varchar column
I have a simple table
CREATE TABLE TRIAL
( "COL" VARCHAR2(20 BYTE)
)
and I insert two values there: '0' and 'A'.
Request
select * from trial order by col
returns
A
0
in that order, and the request
select * from trial where col>'A'
returns no results. What could be causing this behavior, and is there some simple trick without changing the db config to bring order by
and >
in line with each other?
EDIT:
to respond to comments:
select * from v$parameter where name like 'nls_sort'
returns
and
select dump(col,16),col from trial
returns
Typ=1 Len=1: 30 0
Typ=1 Len=1: 41 A
source to share
It must be sorted by the binary / ASCII value of the string.
Translation of values
-
0
=>48
-
A
=>65
When you sort by col
, the default is ascending, so I expect to 0
be first and then A
.
When you ask> "A", you are asking> 65, and neither "A" nor "0" is anymore, so it makes sense.
As mentioned in the comments, I would check your type NLS_SORT
to see if there is anything strange to sort:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams130.htm#REFRN10127
You can also make sure this matches your value NLS_COMP
:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams120.htm#REFRN10117
You can find more information in this answer:
source to share
NOTE . The actual problem turned out to be with the parameter NLS_SORT
. Have a look at Oracle - Case Insensitive Sorting and Comparison / for a good hold and understanding of a particular parameter value.
Actual problem
The problem is related to the parameter value being NLS_SORT
changed to 'WEST_EUROPEAN'
from 'BINARY'
.
Customization
SQL> CREATE TABLE TRIAL
2 ( "COL" VARCHAR2(20 BYTE)
3 );
Table created.
SQL> INSERT INTO trial(col) VALUES('0');
1 row created.
SQL> INSERT INTO trial(col) VALUES('A');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM trial ORDER BY col;
COL
--------------------
0
A
NLS parameter values
SQL> SHOW PARAMETER NLS_SORT;
NAME TYPE VALUE
------------------------------------ ----------- ------
nls_sort string BINARY
SQL> SHOW PARAMETER NLS_COMP;
NAME TYPE VALUE
------------------------------------ ----------- ------
nls_comp string BINARY
Change the parameter value NLS_SORT
:
SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN';
Session altered.
Replay error
SQL> SELECT * FROM trial ORDER BY col;
COL
--------------------
A
0
So now the sorting of the values has changed with the change in the parameter value NLS_SORT
.
source to share