SQL ORDER BY a string value: What is it comparing? (Case sensitive?)-CodePudding

I would like to know what exactly SQL is comparing when we use the ORDER BY statement. More specifically, I'm interested in the comparison when it compares string. Supposedly it is sorting it alphabetically, but what is it actually comparing?

My hunch tells me it could be comparing ASCII values of the characters starting from the left, which would also imply that the sorting is case sensitive ('Btest' would be smaller than 'atest'), but I am unable to find a source confirming this.

CodePudding user response：

This type of question is simple to test.
Here I test is dbFiddle : you can run the same script, with other values if required in your database to check with your local settings, collation etc.

create table sortable(
val varchar(10));
insert into sortable values
('A'),('B'),('a'),('b');

✓

✓

SELECT val
FROM sortable
ORDER BY val;

| val |
| :-- |
| A   |
| a   |
| B   |
| b   |

db<>fiddle here

CodePudding user response：

If you want to switch to ASCII Code sorting you can change the order to sort by bynary value of the characters with the following:

SELECT id, random_varchar FROM table ORDER BY NLSSORT(random_varchar, 'NLS_SORT = BINARY')