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.
7 Answers
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.
You need to use the wildcard % :
SELECT * from games WHERE (lower(title) LIKE 'age of empires III%'); 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%' 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%'; If you used "like" operator without %, the like work as the equal (=), that's mean your code will be as this:
select * from games where lower(title)= 'age of empires iii'; So it doesn't find any matched data then nothing returned.
But if you want to compare only part of the title with your parameter, you should use % sign. You can use it in different positions and this depends on the logic of the database.
Usually it used in the begin and end of the parameter, and the code be like this:
select * from games where lower(title) like '%age of empires iii%'; This will search about the value in the title column even if there is a non-match characters in the beginning or in the end or both. The important condition it will search about is that the value send as argument to the like operator (in this case the value is "age of empires iii") found somewhere in the title field (in this case the title is "Age of Empires III: Dynasties"), and this condition is true, so this row and and any other row with same case will be returned.
In SQL %---% matches any character in between. select * from table_name where column_name line '%kk%'; will match any string that includes 'ss' for instance:
- llssll
- ssll If you want to match the start of the string you should use 'ss%' instead.
select * from table_name where column_name line 'ss%';this query will return only ssll.
WHERE CustomerName LIKE 'a%' --Finds any values that start with "a"
WHERE CustomerName LIKE '%a' --Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' --Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' --Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a__%' --Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' --Finds any values that start with "a" and ends with "o"
-- Case insensitive 2 SELECT * FROM my_table WHERE upper(my_column) LIKE 'SEARCHED %'; -- starts with
3 SELECT * FROM my_table WHERE upper(my_column) LIKE '% SEARCHED'; -- ends with
4 SELECT * FROM my_table WHERE upper(my_column) LIKE '%SEARCHED%'; -- contains
