0

When I use below query

select model from client_source order by model;

the resulting data is in this order

model
______
1234
4g
default
unknown
default1
ipad
iphone simulator
ipod touch
iphone
ipod
test

but I want the sorting in below order

model
________

1234
4g
default
default1
ipad
iphone
iphone simulator
ipod
ipod touch
test
unknown
kotha sathish
  • 11
  • 1
  • 1
  • 4
  • This question is not really a duplicate: case-insensitive **searching** and **sorting** are different (even if some parameters play a role in both cases) – Vincent Malgrat Feb 18 '13 at 09:10

1 Answers1

2

I suppose your NLS_SORT parameter is BINARY. In BINARY sort, the strings are sorted as their binary expression, which means that the uppercase letters will be coming before the lowercase letters:

SQL> alter session set nls_sort=binary;

Session altered

SQL> select model from client_source order by model;

MODEL
--------------------
1234
4g
Default
Unknown
default1
ipad
iphone
iphone simulator
ipod
ipod touch
test

You can change this behaviour with different NLS_SORT values. Use a value of NLS_SORT with the _CI suffix to make it case-insensitive:

SQL> alter session set nls_sort=latin_ci;

Session altered

SQL> select model from client_source order by model;

MODEL
--------------------
Default
default1
ipad
iphone
iphone simulator
ipod
ipod touch
test
Unknown
1234
4g

You can move the digits at the front with a CASE:

SQL> SELECT model
  2    FROM client_source
  3   ORDER BY CASE WHEN regexp_like(model, '^[[:digit:]]')
  4                    THEN 0
  5                    ELSE 1
  6            END,
  7            model;

MODEL
--------------------
1234
4g
Default
default1
ipad
iphone
iphone simulator
ipod
ipod touch
test
Unknown

Additionally, you can use the NLSSORT function to modify the sorting preference of a single query:

select model from client_source order by NLSSORT(model, 'NLS_SORT = LATIN_CI');
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Thank you for your reply!. I am sorry, the values I gave was not correct. Here are the correct values. Model ______ 1234 4G DEFAULT Unknown default1 iPad iPhone Simulator iPod touch iphone ipod test I want the sort order like below. Model ________ 1234 4G DEFAULT default1 iPad iPhone Simulator iphone ipod test Unknown. words with same type should be grouped like iPhone Simulator iphone the above two rows are related to iphone, so they should be one below the other – kotha sathish Feb 15 '13 at 16:44
  • I think you want case-insensitive sort, see my updated answer. – Vincent Malgrat Feb 15 '13 at 16:49
  • 1
    Rather than relying on a specific language maintaining a case-insensitive sort wouldn't it be better to actually use a NLS_SORT parameter that explicitly states this, BINARY_CI for instance, as per http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle/ – Ben Feb 15 '13 at 20:35