SQL like search string starts with

Learning SQL. Have a simple table games with field title. I want to search based on title. If I have a game called Age of Empires III: Dynasties, and I use LIKE with parameter Age of Empires III: Dynasties, everything works fine, the search returns the record with that name. But if I search with Age of Empires III, it doesn't return any records:

SELECT * from games WHERE (lower(title) LIKE 'age of empires III');

This doesn't return anything. Should I be using something else instead of LIKE?

I am using MySQL.

Solution 1:

SELECT * from games WHERE (lower(title) LIKE 'age of empires III');

The above query doesn't return any rows because you're looking for 'age of empires III' exact string which doesn't exists in any rows.

So in order to match with this string with different string which has 'age of empires' as substring you need to use '%your string goes here%'

More on mysql string comparision

You need to try this

SELECT * from games WHERE (lower(title) LIKE '%age of empires III%');

In Like '%age of empires III%' this will search for any matching substring in your rows, and it will show in results.

Solution 2:

You need to use the wildcard % :

SELECT * from games WHERE (lower(title) LIKE 'age of empires III%');

Solution 3:

Aside from using %, age of empires III to lower case is age of empires iii so your query should be:

select *
from games
where lower(title) like 'age of empires iii%'

Solution 4:

COLLATE UTF8_GENERAL_CI will work as ignore-case. USE:

SELECT * from games WHERE title COLLATE UTF8_GENERAL_CI LIKE 'age of empires III%';

or

SELECT * from games WHERE LOWER(title) LIKE 'age of empires III%';