SV: Comparing strings as ints
select floor('5') = floor('6');
TK
-Opprinnelig melding-
Fra: Daniel James [mailto:[EMAIL PROTECTED]]
Sendt: 30. oktober 2001 04:32
Til: [EMAIL PROTECTED]
Emne: Comparing strings as ints
Hi Everyone,
After staring blankly at the mysql manual for a long time, I
thought it
might be time to ask the list...
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?
Thanks,
Daniel
-
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
-
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
Re: Comparing strings as ints
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
Comparing strings as ints
Hi Everyone, After staring blankly at the mysql manual for a long time, I thought it might be time to ask the list... 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? Thanks, Daniel - 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
