On Wed, Apr 2, 2014 at 10:28 AM, Donald Steele <[email protected]> wrote:
> Greetings I am an iOS developer that is much more comfortable with iOS
> than SQLite. I have an iOS app that uses a database with several tables.
> These table the app user uses as directories. The user moves from screen to
> screen making selections from lists generated from my Sqlite database
> (tables). Many of those lists are "sorted" by using order by. In most cases
> that works fine. BUT I have two columns that order by gives "odd" results.
> The columns are highways & exits. Exits I have used Zero fill to get the
> results i need. (Altho I would prefer not using zero fill since it "looks
> odd").
>
> From what I can find out Sqlite uses an ASCII sort in order by, I think
> what I need is a normal or what I call alpha-numeric sort. I gather the way
> to do that is to use the SQlite3_create_collation method. Frankly I can't
> get my arms around this method nor can I find any good explanations or even
> exampls that make sense.
>
> I read some where in my searches that Apple has "canned" versions of that
> method but I can't find those either.
>
> Could someone direct me to some basic tutorials or explanations where I
> can get a better understanding before I dive in to create my own collation?
>
The first thing you need to do is to write down exactly what you want
"equals", "less than", and "greater than" to mean. And whatever you
choose, these definitions need to follow the usual rules:
A < B and B < C implies A < C.
A = B and B = C implies A = C.
A = B implies B = A.
and so forth.
For example, presumably you want "x-123" to sort after "x-23", not before.
That's fine. But do you want "x-012" to be equal to "x-12"? Is "+23"
less than or greater than "-23"? And so forth.
Once you understand *precisely* what you want (and can explain your
definition to us in an email post) then we can help you write the
appropriate collation function.
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
