CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Eloijza Pavlova
Eloijza Pavlova

Reputation: 65

Orace SQL - sorting with all upper case first

What if I wanted sorting to have all the upper case first then the lower case?

A
B
C
D
a
b
c
d

I tried searching the net and all I could find was sorting that would make it

a
A
b
B
c
C

etc..

but I wanted all upper case values sorted first then lower case ones.

Any idea? thanks

Upvotes: 3

Views: 980

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use a case expression to conditionally order the column, based on upper or lower case. Then order by the original column.

select * from tablename
order by case when upper(col) = col then 1 else 2 end, col

Note: The ordering above works well when there is only one character in the string or the when the entire string is either upper case or lower case.

Upvotes: 0

dcieslak
dcieslak

Reputation: 2715

Try to order by the BINARY value of your characters.

SELECT column 
FROM my_table 
ORDER BY NLSSORT(column, 'NLS_SORT = BINARY')

Upvotes: 4

Related Questions