Dennis, I did not know about cast expression but it looks like a better solution. Thank you for your quick response. Jerry > >> > 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
