> I have a table setup with a varchar(20) column, I am using this column to > store a price, the reason I chose varchar(20) is because the prices I am > dealing with are fairly large and I wanted to guarintee 20 digits. There > are no characters like '$' or '.' stored in this column. Now, If I select > and where this column <= somevalue everything is good and works.. but! when > I use a string as somevalue it all goes nuts, because mysql will convert > the string to an integer if the other value in the comparison is an > integer, but if both are strings it's a slight problem. The reason I want > to compare it with a string is the data is from a web based form and > contains an escaped string. I don't want anyone playing with the database. > > Here is the problem another way... > > select '5' <= '6'; > => 1 > select '5' <= '4'; > => 0 > select '5' <= '40'; > => 0 <- eegad..... > > does anyone know how I can get around this?
Daniel, The stated problem has to do with 20-digit numbers stored as varchar data, yet the examples given are one and two character strings. The apparent problem is not due to the comparison operator, but an inaccurate representation of 20-digit numbers using one and two digits! Solving this logic requires a real step back in time. Imagine you are teaching a youngster basic arithmetic with multi-digit numbers and it'll all fall into place... In standard (decimal) arithmetic, using real or integer numbers, the mathematical system 'lines up' dissimilarly-sized (scale) numbers (according to the stated or implied decimal point) and 5 is indeed less than 40 - or (hint, hint) 05.0 is less than 40.0. However strings are 'lined up' differently (from the first character rightwards, not the decimal point leftwards), so when there are dissimilar numbers of digits to the left of the (implied) decimal point, per the example above, comparison logic all turns to custard... (in the example above: 5 'units' (or 'ones', depending upon where you went to school) are being compared with 4 'tens') If it can be assumed that the 20-character numbers are all stored with a stated or implied decimal point with (say) two 'decimal digits' to its right, then string comparisons may continue to be applied, and successfully, PROVIDED both strings are 'normalised' into the same VARCHAR(20) format and are filled with leading zeros or spaces: thus '0...005' is less than '0...040'. If the decimal point is not in a 'fixed' position within the 20 characters, all is not (yet) lost, but you will have your work cut out for you (so a review of the formatting decision would also be appropriate) - please get back to me. Regards, =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
