SQLite Sorting TEXT field

Question

I have the following table:

CREATE TABLE IF NOT EXISTS TEST_TABLE ( testID INTEGER PRIMARY KEY AUTOINCREMENT, testName TEXT);

Some of the test data

1. Test
2. Hello
3. aa
4. World

My Query

SELECT * FROM TEST_TABLE ORDER BY testName

Response:

1. Hello
2. Test
3. World
4. aa

Expected:

1. aa
2. Hello
3. Test
4. World

Can someone explain why this is the response?

Answer 1

That's because ORDER BY is case sensitive and a 'a' is greater than a 'Z'. There is a solution to be case insensitive : [Your request] ORDER BY testName COLLATE NOCASE

Hope this could explain your problem.

Edit : dom explain it before :-)

Answer 2

try this:

SELECT * FROM TEST_TABLE ORDER BY testName COLLATE NOCASE;