jerry wrote: > I have a CHAR field which is usually an integer. I would like to sort > this field as if it is an integer so that 1a 5b 10c 12xxx does not get > sorted as 10c 12xxx 1a 5b. I have successfully used something like > "ORDER BY CHARFIELDNAME - 0" which seems to convert the expression to > an integer the same way that atoi would. This is exactly what I want. I > would like to know if this is an accident or it is behavior that I can > count on for future versions of sqlite. Thank you. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
I would suggest that you use a cast expression to force the data to be an integer. For strings such as your examples the cast will only use the initial characters that form a valid integer. order by cast(somefield as integer) The behavior you are seeing is well defined, but I think a little less clear. The subtraction operator requires numeric arguments so sqlite will coerce the string field into a numeric value before it is passed to the subtraction operator. This coercion works in the same way as cast operator, ie it ignores any non numeric suffix. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
