>From the first post I've got the impression that only first character matters for you. When such sort order should persist over all characters you can't do it with simple query. Only the custom collation can help you.
Pavel On Wed, Dec 15, 2010 at 7:29 AM, Harish CS <cshar...@gmail.com> wrote: > > Hello Pavel, > > Thanks. > The substr() compares the first character only. > For example, if the data is [CAT=$, CAT1$], it has to be sorted as > [CAT1$, CAT=$] because when '=' and '1' are compared, '1' has to come first. > Thanks for any suggestions. > -Harish > > > > Pavel Ivanov-2 wrote: >> >> 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 >> >> > > -- > View this message in context: > http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30463371.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
