Interesting problem comparing strings with integer values...

[Brandon Beck]

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 
consistent.

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 
mind.



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?
> 
> Chris.
>