Interesting problem comparing strings with integer values...
bbeck at NOSPAM.austin.rr.com
Thu Jan 16 07:04:21 CET 2003
After reading through all of the other posts in this thread, I can see
two potential solutions not yet mentioned.
1. Add an additional column to your table that contains the numeric
representation of your data. This column will be used in your queries
for comparison purposes only, while your program will be provided the
string version of the data. This had the disadvantage that your rows
are now one field bigger, and have duplicate data that must remain
2. If you know your set of possible integers and floating point numbers
are reasonably small, you can make an auxillary table that you can join
against that maps a string to its numeric value. This had the advantage
of being nicer on space than the first solution.
I know you've said that you agree with the design decision made to
represent this apparent numeric data as strings, and agree with it, but
be warned that the types of operations you're suggesting be performed on
this string data, namely any type of comparison operation, is going to
be slow, and likely to cause performance issues if your database gets
reasonably large. It may make sense to revisit your design with this in
Chris Spencer wrote:
> Due to certain design constraints, I must be able to store both integers
> and floating point numbers as strings. These strings must be able to be
> compared correctly, so things like: "999"<"3432" are not possible.
> One option we thought of was padding the strings with zeros, so things
> like: "00000999"<"00003432" would work. This seems a bit hack-y to me. I was
> wondering if anyone has a more elegant solution to the problem?
More information about the Python-list