>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 <[email protected]> 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 <[email protected]> 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 >>> [email protected] >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
