different results from '=' vs. 'LIKE'
Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results from '=' vs. 'LIKE'
Do you seriously have a column named 0020,0032 ?!!? And don't even get me started on the actual name of these images (column data). Wow. That makes my head hurt. I think mySQL is just punishing you for both of those offenses. *hee hee* ;-p But if I were to venture a guess, and RTFM... http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html I'd say it might be related to casting. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons. So perhaps in the first case you're dealing with strings, but in the second case you're dealing with numbers? Or possibly you're not escaping your \ enough? To search for “\”, specify it as “”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. I don't know, but that should point you in the right direction and perhaps convince you to rename your column and use a more sane data naming convention... good luck! D.Vin http://daevid.com On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael
Re: different results from '=' vs. 'LIKE'
Also, I realize you're trying to 'encode' some sort of X\Y\Z coordinates in that column, so perhaps a different delimiter such as the pipe | character or , would be more appropriate than a \ which has special meanings? Or possibly just split them out into separate X, Y, Z columns rather than cramming them together like that. This would allow you to do various trig and math functions on them easier (assuming you are storing coordinates for a reason). There are basic SQL 'update' statements you could write to fix your existing data and/or convert it to the new delimiter. This may save you headaches going forward. d. On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael
Re: different results from '=' vs. 'LIKE'
On Dec 9, 2008, at 2:03 PM, Daevid Vincent wrote: Do you seriously have a column named 0020,0032 ?!!? And don't even get me started on the actual name of these images (column data). Jepp, and there are a hell of a lot of more weird number like that. That's an attribute tag from DICOM images. The names might alter, the tag value won't. Wow. That makes my head hurt. I think mySQL is just punishing you for both of those offenses. *hee hee* ;-p But if I were to venture a guess, and RTFM... http://dev.mysql.com/doc/refman/5.1/en/string-comparison- functions.html I'd say it might be related to casting. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons. So perhaps in the first case you're dealing with strings, but in the second case you're dealing with numbers? Or possibly you're not escaping your \ enough? Yeah, I actually figured that out after writing the first mail. Then I was talking to myselfe for quite a while like: freakin' 4 backslashes just to get one out of in the end, tsss. To search for “\”, specify it as “”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. I don't know, but that should point you in the right direction and perhaps convince you to rename your column and use a more sane data naming convention... good luck! Thx for the feedback and cooperation! :) Cheers, Michael D.Vin http://daevid.com On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results from '=' vs. 'LIKE'
On Dec 9, 2008, at 2:27 PM, Daevid Vincent wrote: Also, I realize you're trying to 'encode' some sort of X\Y\Z coordinates in that column, so perhaps a different delimiter such as the pipe | character or , would be more appropriate than a \ which has special meanings? That's actually how the values are in original. At this stage, I don't want to mess around with it further. But exporting it later to another coordinate-table, if that'll be required, is being thought of. Perhaps the field might also get dropped. (Ups, now the wholly god of db schema design will put rage upon me for bad designing in the first place ... not to mention the NU** values ... :-$) Thx for the hint! :) Cheers, Michael Or possibly just split them out into separate X, Y, Z columns rather than cramming them together like that. This would allow you to do various trig and math functions on them easier (assuming you are storing coordinates for a reason). There are basic SQL 'update' statements you could write to fix your existing data and/or convert it to the new delimiter. This may save you headaches going forward. d. On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
