How to order by a VARCHAR column lexicographically

The default order by of VARCHAR columns in Oracle is not lexicographic.
For example when I execute the query:

select * from
  (select 'a' from dual) union all
  (select '/a' from dual) union all
  (select '/b' from dual) union all
  (select '_b' from dual) union all
  (select '_a' from dual) union all
  (select 'b' from dual)
  order by 1

I will get the following result:

/a
_a
a
/b
_b
b

While in a lexicographic order by I will expect:

/a
/b
_a
_b
a
b

Is there a way to make the order by compare lexicographically?
ElishaMosheAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ElishaMosheConnect With a Mentor Author Commented:
You are right...
I found out that the cause for the problem is JDBC and I used a JDBC sql editor thats why I thought the problem was in the DB.

For some reason when using JDBC the NLS_SORT value is not BINARY while the default value in the database is BINARY. This causes the order by to display the results as shown above.

This can be fixed by altering the session of the JDBC connection:
alter session set nls_sort = BINARY;

or by modifiying the order by clause and adding NLS_SORT=BINARY, for example:

select b.a as ba from
  ((select 'a' as a from dual) union all
  (select '/a' as a from dual) union all
  (select '/b' as a from dual) union all
  (select '_b' as a from dual) union all
  (select '_a' as a from dual) union all
  (select 'b' as a from dual)) b
  order by NLSSORT(ba, 'NLS_SORT=BINARY')

Thank you.
0
 
Jinesh KamdarConnect With a Mentor Commented:
When I run it, I get it exactly the way u expect it!
SQL> select * from 
  2    (select 'a' from dual) union all
  3    (select '/a' from dual) union all
  4    (select '/b' from dual) union all
  5    (select '_b' from dual) union all
  6    (select '_a' from dual) union all
  7    (select 'b' from dual)
  8    order by 1;
 
'A'
--------------------------------
/a
/b
_a
_b
a
b
 
6 rows selected.
 
SQL> 

Open in new window

0
All Courses

From novice to tech pro — start learning today.