Re: [sqlite] SQL query on sort order

If not already done creating a page of additional collations on the wiki would make sense and minimize work all around. > I just mailed you an extension for SQLite offering the collation you need. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQL query on sort order

Harish, >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

Re: [sqlite] SQL query on sort order

>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 wrote:

Re: [sqlite] SQL query on sort order

Harish CS wrote: > Collation is okay for case insensitivity but since here we have digits and > special chars I think it may not be helpful. Plz correct me if I am wrong. You can implement a custom collation that defines any order of your choice. See

Re: [sqlite] SQL query on sort order

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

Re: [sqlite] SQL query on sort order

Pavel Ivanov: Thank you very much. We used the query. -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 >

Re: [sqlite] SQL query on sort order

Kishor: Collation is okay for case insensitivity but since here we have digits and special chars I think it may not be helpful. Plz correct me if I am wrong. Puneet Kishor-2 wrote: > > > > Harish CS wrote: >> Hi, >> We have a problem with a sql query. >> In a table, a column called "name"

Re: [sqlite] SQL query on sort order

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 '|' ||

Re: [sqlite] SQL query on sort order

Harish CS 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 >