类似 SQL 的搜索字符串以

学习 SQL。有一个简单的桌面游戏与字段标题。我想根据标题进行搜索。如果我有一个叫做 Age of Empires III: Dynasties的游戏，我使用带有参数 Age of Empires III: Dynasties的 LIKE，一切都很好，搜索返回带有该名称的记录。但是如果我用 Age of Empires III搜索，它不会返回任何记录:

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

这不返回任何内容。我应该使用其他内容而不是 LIKE吗？

我正在使用 MySQL。

298483 次浏览
小开

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%';
小开

You need to use the wildcard % :

SELECT * from games WHERE (lower(title) LIKE 'age of empires III%');
小开
最佳答案 
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.

小开

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%'
小开

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
  • 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.
小开

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.

小开

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


提交答案