Sql query to populate iPhone quick-access-bar (count rows by first letter)

Question

I have a sqlite database, with potentially tens of thousands of rows, which is exposed as a UITableView on the iPhone. There's many columns in the relevant table, but for now I only care about a 'name' column. (The database is in a server app, the iPhone app is a client, talking over a socket) Naturally to make this usable, I want to support the 'quick access bar', and hence to count the number of rows that start with A, B, C .. Z (and ideally handle the rows that start with an awkward characters such as #, or a digit).

I'm having trouble formulating a SQL query (supported by SQLite) to count the rows starting with a given letter; if necessary I could make 26 separate queries, but I wonder if there's some nested query magic to compute all the values at once. (Obviously 'COUNT WHERE NAME LIKE 'A*' would work, repeated twenty-six times, but it feels very crude, and wouldn't handle digits or symbols)

Relevant facts: modifications to the database are infrequent, and I can easily cache the query, and refresh it when the DB is modified. The DB is much larger than the RAM on the server device, so avoiding paging the whole DB file would be good. There is an index on the relevant 'name' column of the DB already. Performance is more important than brevity, so if separate queries is faster than one complex query, that's fine.

Answer 1

Use substr(X,Y,Z) instead of LIKE.

substr(X,Y) The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.