I have this table with a 'title' field which is varchar2 and I want to select all rows and sort them first by number and then by the alphabet as it normally happens.
For instance, I currently get this using a simple ORDER BY title in the end:
But I want this:
The weird thing is that SQL Developer shows the "right" order, with numbers first. But on my app (PHP using OCI8) it shows numbers last.
Not an Oracle expert, but you are supposed to be able to do it without altering the session with
SELECT * FROM my_data
ORDER by NLSSORT(title,’NLS_SORT=BINARY_AI’)
where you can change the NLS_SORT= to fit your needs (here are the list of values)
Keep in mind that docs says that this will force table scan, so it might be beneficial to filter them first (but if you are selecting all the table scan is what you are going to use anyway).
The reason why SQL Developer exhibits different behaviour is probably because it changes the session.
It's also possible to change this for the whole session:
alter session set nls_sort=binary;
the difference in behaviour that you're seeing is probably because of different NLS_SORT parameter setting. Consider:
SQL> select * from nls_session_parameters where parameter='NLS_SORT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT BINARY
SQL> SELECT * FROM my_data order by title;
TITLE
-----
321
Abc
Def
SQL> alter session set nls_sort=french;
Session altered
SQL> SELECT * FROM my_data order by title;
TITLE
-----
Abc
Def
321
You can build a query that should give you the expected result regardless of your NLS_SORT session parameter setting, for example:
SQL> SELECT *
2 FROM my_data
3 ORDER BY CASE
4 WHEN regexp_like(title, '[0-9]+\.?[0-9]*') THEN
5 1
6 ELSE
7 2
8 END, title;
TITLE
-----
321
Abc
Def
