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

Answer 1

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)

Answer 2

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).