1

Intro

I have been using new databases such as mysql and mariaDB for the last few years.

For a project I am now using oracle after many years and I was surprised to see that Oracle order by is case sensitive... UpperCase characters go above LowerCase characters for example in ASC.

Is this behavior following the SQL specifications? Do the general SQL language specs outline what the behavior should be or is it up to each vender?

I'm asking because many years of using mysql/mariaDB made me consider order by to be case insensitive.

Mysql

I see for example that in the documentation for mysql:

On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.

From: http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

Menelaos
  • 23,508
  • 18
  • 90
  • 155
  • 3
    Depends on the data's collation. – jarlh Jun 30 '16 at 10:13
  • @ jarlh Interesting... mind expanding more on that? For people reaching my question in the future :) – Menelaos Jun 30 '16 at 10:14
  • 3
    Unfortunately, it can be a *vast* topic. E.g. here's the section from the [mysql manual](http://dev.mysql.com/doc/refman/5.7/en/charset.html). And it's not so much "product X does A and product Y does B" as how each product acts *by default*. You can get case sensitive sorting in any decent SQL database system. And you can get case insensitive sorting in any decent SQL database system. – Damien_The_Unbeliever Jun 30 '16 at 10:18
  • I don't consider myself a newbie developer after over 10 years of writing code but it's really is a kick in the nutts when stuff like this pops up. Danger of getting accustomed to specific vendor and then your paradigm/assumptions get ya if the technology changes. – Menelaos Jun 30 '16 at 10:22
  • [Case insensitive searching in Oracle](http://stackoverflow.com/q/5391069/1509264) – MT0 Jun 30 '16 at 10:29
  • 1
    Oracle documentation for [Linguistic Sorting and String Searching](https://docs.oracle.com/cd/B28359_01/server.111/b28298/ch5lingsort.htm) – MT0 Jun 30 '16 at 10:35
  • 1
    They have decade's old code bases, prior rev compatibility issues, and futuristic differentiators (use ours not theirs). What is the complexity of any of this? This is an off-topic Quora dot com type question. – Drew Jun 30 '16 at 22:44

1 Answers1

4

From Oracle Database SQL Language Reference, 12c Release 1 (12.1):

When character values are compared linguistically for the ORDER BY clause, they are first transformed to collation keys and then compared like RAW values. The collation keys are generated either explicitly as specified in NLSSORT or implicitly using the same method that NLSSORT uses. Both explicitly and implicitly generated collation keys are subject to the same restrictions that are described in "NLSSORT" on page 7-207. As a result of these restrictions, two values may compare as linguistically equal if they do not differ in the prefix that was used to produce the collation key, even if they differ in the rest of the value.

Hope, this will help you!

Have a nice day!

Hasan Alizada
  • 591
  • 4
  • 13