SQL not matching string

Question

This error first occurred in a php function, but after a while of troubleshooting I am getting same results in phpmyadmin.

I have a simple SQL statement SELECT * FROM tbl_movie WHERE title= "movie"

And I get nothing returned, I am copying and pasting (also tried typing it in) from the table. So there should be a match. Tried this with a many values that should work, same result (0 rows returned).

Oddly enough SELECT * FROM tbl_movie WHERE title LIKE "%movie%". Returned the desired row.

What is going on here?

Edit: I have tried everything I can think of to weed out the possibility of spaces messing it up. Sae results with: spaces on each/both sides, items with/without spaces.

Can you show the row? Any white space around it or other chars? — wazy
– wazy, Commented Nov 9, 2013 at 2:27
perhaps you have a space or some other character in the row you are searching for? — dnapierata
– dnapierata, Commented Nov 9, 2013 at 2:28

O. Jones · Accepted Answer · 2013-11-09 02:40:01Z

3

You should try single quotes rather than double quotes.

If you get the same results, there is probably white space either before or after the word 'movie' in that column. Try this to figure that out. IF the length is something other than 5, that's your explanation.

 SELECT LENGTH(title), HEX(title), title
   FROM tbl_movie
  WHERE title like '%movie%'

edited Nov 9, 2013 at 2:40

answered Nov 9, 2013 at 2:29

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dan Over a year ago

So I tried that and it returned 6 as the length instead of 5. But I tried the query with spaces on each side.

O. Jones Over a year ago

This means there is some undisplayable character either before or after the word 'movie'. That character doesn't have to be a space.

O. Jones Over a year ago

Try SELECT HEX(title), title . You'll get a hex dump of the character in the title. This might help you figure out the rubbish character.

Dan Over a year ago

AHA, there is '0D' at the end of the hex values for these strings.

leoledmag · Accepted Answer · 2013-11-09 02:33:06Z

0

Please test this code:

SELECT * FROM `tbl_movie` WHERE `title` LIKE '%"movie"%'

See the "" and change for ' or `.

Sometimes the single and double quotes are problems when performing searches in mysql.

answered Nov 9, 2013 at 2:33

leoledmag

1361 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL not matching string

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related