Interesting problem comparing strings with integer values...
bkelley at wi.mit.edu
Thu Jan 16 15:09:20 CET 2003
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?
From the MYSQL documentation the following trick might be useful
(although maybe not on oracle):
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.
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);
More information about the Python-list