I have a string that is defined as one or more dot-separated integers like 12345, 543.21, 109.87.654, etc. I'm storing values in a MySQL database and then need to find the rows that compare with a provided value. What I want is to select rows by comparing each component of the string against the corresponding component of the input string. With standard string comparison in MySQL, here's where this breaks down:

This is natural because the string comparison is a "dictionary" comparison that doesn't account for string length, but I want a 0 result on the second query.

Is there a way to provide some hint to MySQL on how to compare these? Otherwise, is there a way to hint to ActiveRecord how to do this for me? Right now, the best solution I have come up with is to select all the rows and then filter the results using Ruby's split and reject methods. (The entire data set is quite small and not likely to grow terribly much for the foreseeable future, so it is a reasonable option, but if there's a simpler way I'm not considering I'd be glad to know it.)

I don't think this will work either, but it's a nice idea. Here's where I think this would break down. 543.21.9 > 543.2.19 should be true, but using your method, it will be equal.
–
jxpx777Jul 24 '10 at 16:27

I'm accepting this one because I think it's the closest thing to the answer. We ended up going a slightly different direction, but this is a good start.
–
jxpx777Nov 22 '10 at 13:47

This indeed only works for valid floats. Doing it for more then 1 dot would require a LOT of comparing of SUBSTRING_INDEX(SUBSTRING_INDEX(field, '.', <positionnumber you're comparing>), '.', -1) (with a manual repeat for the maximum number of position's you are comparing)

That works when there are only two components, but if there are three or more it breaks: mysql> SELECT '543.21.5'+0 >= '543.21.5'+0; +------------------------------+ | '543.21.5'+0 >= '543.21.5'+0 | +------------------------------+ | 1 | +------------------------------+ 1 row in set, 2 warnings (0.00 sec)
–
jxpx777Jul 16 '10 at 15:48