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
