If you want to do that completely in SQL without using collations you can do something like this:
select name, case when substr(name, 1, 1) between 'A' and 'Z' or substr(name, 1, 1) between 'a' and 'z' then upper(name) when susbtr(name, 1, 1) between '0' and '9' then '|' || upper(name) else '~' || upper(name) end as sort_col from mytable order by sort_col; (I used the fact that '~' goes after '|' in ASCII) But of course you can achieve much better performance with collation or user-defined function implementing all this logic. Pavel On Tue, Dec 14, 2010 at 9:16 AM, Harish CS <cshar...@gmail.com> wrote: > > Hi, > We have a problem with a sql query. > In a table, a column called "name" contains character data that may include > alpha, numeric and special characters. It is required to sort in such a way > that names starting with alpha characters are listed first, then numerals > and finally special characters. > For example, the column contains data: [Bhaskar, 5th user, anand, ##, Anand, > bhaskar ]. > When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th user, > ##]. > The query "select name from mytable order by name asc;" lists data in this > order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not correct. > Any ideas on this? All thoughts are welcome. > -Thanks > Harish > -- > View this message in context: > http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30455015.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users