Interesting problem comparing strings with integer values...

[Brian Kelley]

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

 From the MYSQL documentation the following trick might be useful 
(although maybe not on oracle):

http://www.mysql.com/doc/en/Cast_Functions.html
...
If you are using a string in an arithmetic operation, this is converted 
to a floating-point number.
...

Which means that to do comparisons, simply add 0 to your string values. 
  See below.


create table foo (   a integer not null,   b varchar(60) );
insert into foo values (1, '234');
select * from foo where foo.a < (foo.b + 0);