0

I have 2 tables in my database. Both tables have a field called listing_key. In one table if I open phpMyAdmin I can go to search and select a distinct row based on a query similar to the following:

SELECT * FROM properties WHERE listing_key='MYLISTINGKEY'

That query returns the expected result and all is well

The second table only has 2 fields... an AI id field and the same listing_key field.

If I run the same query on the second table if returns no result. However, and there is where it gets interesting, if I change the query to a LIKE statement instead of an equals it returns the correct result.

I have checked, both tables have the same setup for the listing_key fields (varchar(80), utf8_general_ci). Both tables are also MyISAM tables.

I have no idea why I am being forced to query table 2 using a LIKE operator instead of using equals.

Any ideas or thoughts?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Terry Carter
  • 298
  • 2
  • 11

1 Answers1

2

This issue turned out to be a problem caused by MS Excel adding a trailing \r to each of my fields.

Terry Carter
  • 298
  • 2
  • 11
  • Still there should be no difference at all using `listing_key = 'MYLISTINGKEY'` or `listing_key LIKE 'MYLISTINGKEY'`. So I'd consider this a bug. (Or have I misunderstood and you used `listing_key LIKE '%MYLISTINGKEY%'` instead?) – Thorsten Kettner Jun 06 '16 at 22:32
  • I'm using the table to purge against another table in a not in clause so it was important for the data to be exact – Terry Carter Jun 06 '16 at 22:39
  • Yes, and what I am saying is that `listing_key LIKE 'MYLISTINGKEY'` should check for exact matches, as you are not using any wildcard (`%` or `_`). `LIKE` should work exactly like `=` in this situation. If it doesn't, I consider this a bug. – Thorsten Kettner Jun 06 '16 at 22:41
  • @ThorstenKettner: `=` is not sensitive to trailing whitespace, whereas `LIKE` is. – eggyal Jun 06 '16 at 23:05
  • @eggyal: Thank you. I didn't know that. I tried it in SQL fiddle (http://sqlfiddle.com/#!9/9eecb7d/62851) and read a reasoning in the SQL Server docs (http://stackoverflow.com/questions/4166159/sql-where-clause-matching-values-with-trailing-spaces) where the behavior is the same. So `LIKE` is even *more* strict than `=` when it comes to detecting equality. And replacing `=` with `LIKE` wouldn't have resulted in finding the row that was missing before. The OP forgot to tell us that the search string was also altered, so it was quite natural for the queries to return different results after all. – Thorsten Kettner Jun 07 '16 at 07:35