Sorting Issue with apostophes using ORDER BY

[Matumba2]

There's an issue using ORDER BY when the column value contains an apostrophe. For example i'm saving song titles in my database.

For example i have three entrys with the following values:

1. I'm Coming For Youri Soul
2. If I Play Your Game
3. Inca

When using ORDER BY title ASC, the correct order should be:

1. If I Play Your Game
2. I'm Coming For Your Soul
3. Inca

But the given order is:

1. I'm Coming For Your Soul
2. If I Play Your Game
3. Inca

I think that's a General Problem in SQL because with SQLite for C++ there's tue same issue. Or is there any workaround?

[ColonelDuddits]

For performance reasons most DBM systems, SQLite included, use binary comparison to sort results. Apostrophes (and almost all punctuation) are less than any upper- or lower- case character. I can think of at least three ways to get around this:

1. Change the database's collation setting to something that supports linguistic comparison.
2. Explicitly specify a linguistic collation in the order by clause.
3. Omit the order by and use LINQ to order the results.

Option 3 is probably the simplest and it has the advantage that, if done properly, it will order the results according to rules of the calling user's locale.

[praeclarum]

You can in fact change the collation but I don't think any of the built in ones will work for you.

Yeah, call ToList().OrderBy(...) to get current culture sorting.