How can I sort by a table column in varying cases (Oracle)

sql oracle select sql-order-by case-sensitive
acidRain picture acidRain · Mar 20, 2012 · Viewed 22k times · Source

How can I sort a table with a column of varchar2 with characters in varying cases (UPPER and lower)?

For example, when I do an order by of the Name column, I get the following results:

ANNIE
BOB
Daniel
annie
bob

What I want is something like this:

ANNIE
annie
BOB
bob
Daniel

Answer

Michael Durrant picture Michael Durrant · Mar 20, 2012

Use lower(field), e.g. 

select * from tbl order by lower(name)

If you need to address special characters for non-english languages then the other answers about NLSSORT may be what you need. If you don't I would try and KISS and use lower() as it is very easy to remember and use and be read by others (maintainability).

Related questions

DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More
How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Read More