'First order by uppercase & lowercase

I have a few records which start with lowercase & uppercase.

SELECT * 
FROM wording 
ORDER BY word ASC;

Lanza Mensi Mhiob blackbery umbre apple Etios Iomio

I am trying to order this by A-Z and a-z, something like
```none
Etios
Iomio
Lanza
Mensi
Mhiob
apple
blackberry
umbre

So, all the word which is starting with capital come first and after then all lowercase words.

mysql


Solution 1:[1]

This should work too:

select * 
from wording 
order by binary(word) ASC;

Result:

word
Etios
Iomio
Lanza
Mensi
Mhiob
apple
blackbery
umbre

Demo

The binary sorting would put capital letters before lowercase ones

Solution 2:[2]

you can use ASCII Method to get desired result. It will sort Value on the basis of Numeric ASCII Value of Character it means if we Sort data ascending it will sort Capital Letter First. ASCII of (A-Z: 65-90) and ASCII of (a-z: 97-122)

SELECT * FROM wording ORDER BY ASCII(word) ASC;

Output:

Etios
Iomio
Lanza
Mensi
Mhiob
apple
blackbery
umbre

enter image description here

Reference Website:SQL Practice

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ergest Basha
Solution 2

Related Questions