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
