Sql ORDER BY alphabetically no casesensitive

Question

Today I encountered problem that causes difficulty for me to solve it.

In application I want to display records in aphabetical order thus in SQL statement I'am using ORDER BY,
But it looks like CAPITAL letters are before lowercase letters so record starting with Z is before a.

This is example of my sql statement

SELECT * FROM myTable WHERE id= 5 ORDER BY name

Do you have any ideas ? Can I sort data in DataTable object after retreiving it from database ? or can it be accomplished by more complex sql statement?

Any ideas will be appreciated

your question looks an awful lot like this question. http://stackoverflow.com/questions/9788838/how-can-i-sort-by-a-table-column-in-varying-cases-oracle — DidIReallyWriteThat, Apr 15 '14 at 12:22

score 6 · Answer 1 · answered Apr 15 '14 at 12:21

6

You can modify your SQL query in such a way that all capitals are transformed to lower before ordering

SELECT * FROM myTable WHERE id = 5 ORDER BY LOWER(name)

answered Apr 15 '14 at 12:21

Michael Mairegger

6,833
28
41

score 2 · Accepted Answer · answered Apr 15 '14 at 12:25

The rules for comparing text values is the collation; there are many many collations available in SQL Server, and most have both case-sensitive and case-insensitive options.

If you don't want to change the collation (in particular, if this applies only to specific cases), you can also use functions like LOWER / UPPER, but this cannot make efficient use of indexes. A hybrid approach is to store redundant information: store the original data in one column, and the standardized data (perhaps all lower-case-invariant) in a second column. Then you can index the two separately (as you need), and operate on either the original or standardized data. You would normally only display the original data, though. Persisted+calculated+indexed columns might work well here, as then it is impossible to get inconsistent data (the server is in charge of the calculated column).

Thanks for usefull informations, that maybe handy in future but at the moment I'm working on old existing data and i can't change data structure — user3455363, Apr 15 '14 at 13:40
@user3455363 oh, if only I had a 1GBP every time I heard "I need it to behave differently, but I'm not allowed to change anything". Well... if you don't change anything: expect it to stay the same ;p — Marc Gravell, Apr 15 '14 at 13:42

score 1 · Answer 3 · answered Apr 15 '14 at 12:22

1

Try

   SELECT * FROM myTable WHERE id= 5 ORDER BY LOWER(name)

OR

   SELECT * FROM myTable WHERE id= 5 ORDER BY LCASE(name)

depending on which database you are using

answered Apr 15 '14 at 12:22

Think Different

2,815
1
12
18

I've tried LCASE , LOWER , UCASE,UPPER none of them worked it throws exception : System.Data.OleDb.OleDbException: SQL: ORDER BY clause is invalid. `string sql = "SELECT * FROM app_lookup WHERE nid="+npk+" ORDER BY LCASE(cdesc)";` – user3455363 Apr 15 '14 at 13:06

score 1 · Answer 4 · answered Apr 15 '14 at 12:22

You can perform ordering by providing case in SQL. Just do this:

SELECT * FROM myTable WHERE id= 5 ORDER BY UPPER(name)

OR

SELECT * FROM myTable WHERE id= 5 ORDER BY UCASE(name)

Ordering will be done on upper case name while you result will be same as present in table.

score 0 · Answer 5 · answered Apr 15 '14 at 13:11

0

Try this...

SELECT * FROM myTable WHERE id= 5 ORDER BY name COLLATE Latin1_General_100_CI_AS

answered Apr 15 '14 at 13:11

Ranadeera Kantirava

324
3
11

Sql ORDER BY alphabetically no casesensitive

5 Answers5