Escaping colon in mysql LIKE query

Question

I have this mysql query which searches for a specific word. For example lets make a search for Killzone Shadow Fall(stored in a variable):

select * from games where title like 'Killzone Shadow Fall'

It returns empty. Though this entry exist in the database as 'Killzone: Shadow Fall'

What should be done in order to return 'Killzone: Shadow Fall' when someone searches for 'Killzone Shadow Fall'?

score 1 · Accepted Answer · edited Apr 22 '23 at 08:26

1

You don't need to escape colon, try changing to:

select * from games where title like 'Killzone%'

This will return all records from table where title field's value starts with Killzone.

edited Apr 22 '23 at 08:26

halfer

answered Jul 06 '14 at 03:19

Sudhir Bastakoti

Ahhh I see.. I had it before and after the search value. Thanks it works now :) – user3672357 Jul 06 '14 at 03:29
It needed a bit more modification though. All I had to do was replace white spaces with % – user3672357 Jul 06 '14 at 03:32
Problem with this method is if an user searches for "Halo 5", it'd return all records staring with Halo – J A Jul 06 '14 at 03:32
Yes to fix that, I replaced the spaces with % – user3672357 Jul 06 '14 at 03:34

score 0 · Answer 2 · answered Jul 06 '14 at 03:23

0

The _ character in LIKE replaces only 1 character in a string :

select * from games where title like 'Killzone_ Shadow Fall'

But it would be preferable to use % to have a larger set of occurence matching your search.

answered Jul 06 '14 at 03:23

Oliboy50

score 0 · Answer 3 · answered Jul 06 '14 at 03:27

0

I'd suggest replacing " " with %.

$searchTerm = "Killzone Shadow Fall';

$query = 'SELECT * FROM games WHERE title LIKE "'.str_replace(' ','%',$searchTerm).'"';

answered Jul 06 '14 at 03:27

J A

3 Answers3