MySQL SQL LIKE 通配符空格字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9946219/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL LIKE wildcard space character
提问by user765368
let's say I have a string in which the words are separated by 1 or more spaces and I want to use that string in and SQL LIKE condition. How do I make my SQL and tell it to match 1 or more blank space character in my string? Is there an SQL wildcard that I can use to do that?
假设我有一个字符串，其中的单词由 1 个或多个空格分隔，我想在 SQL LIKE 条件中使用该字符串。如何制作我的 SQL 并告诉它匹配字符串中的 1 个或多个空格字符？是否有我可以使用的 SQL 通配符？
Let me know
让我知道
回答by markblandford
If you're just looking to get anything with atleast one blank / whitespace then you can do something like the following WHERE myField LIKE '% %'
如果您只是想获得至少一个空白/空格的任何东西，那么您可以执行以下操作 WHERE myField LIKE '% %'
回答by Jay
If your dialect allows it, use SIMILAR TO, which allows for more flexible matching, including the normal regular expression quantifiers '?', '*' and '+', with grouping indicated by '()'
如果您的方言允许，请使用 SIMILAR TO，它允许更灵活的匹配，包括正常的正则表达式量词“?”、“*”和“+”，分组由“()”指示
where entry SIMILAR TO 'hello +there'
will match 'hello there'with any number of spaces between the two words.
将与'hello there'两个单词之间的任意数量的空格匹配。
I guess in MySQL this is
我猜在 MySQL 中这是
where entry RLIKE 'hello +there'
回答by Seth G
I know this is late, but I never found a solution to this in relation to a LIKEquestion.
我知道这已经晚了，但我从未找到与LIKE问题相关的解决方案。
There is no way to do what you're wanting within a SQL LIKE. What you would have to do is use REGEXPand [[:space:]]inside your expression.
没有办法在SQL LIKE. 你必须做的是使用REGEXP和[[:space:]]在你的表达中。
So to find one or more spaces between two words..
所以要在两个词之间找到一个或多个空格..
WHERE col REGEXP 'firstword[[:space:]]+secondword'
回答by shriek
Another way to match for one or more space would be to use [].
It's done like this.
另一种匹配一个或多个空间的方法是使用[]. 它是这样完成的。
LIKE '%[ ]%'
This will match one or more spaces.
这将匹配一个或多个空格。
回答by dldnh
you can't do this using LIKE but what you can do, if you know this condition can exist in your data, is as you're inserting the data into the table, use regular expression matching to detect it up front and set a flag in a different column created for this purpose.
你不能使用 LIKE 来做到这一点，但是你可以做的是，如果你知道这种情况可以存在于你的数据中，那就是当你将数据插入表中时，使用正则表达式匹配来预先检测它并设置一个标志在为此目的创建的不同列中。
回答by Francois
I just replace the whitespace chars with '%'. Lets say I want to do a LIKE queryon a string like this 'I want to query this string with a LIKE'
我只是用 '%' 替换空白字符。假设我想对这样的字符串进行LIKE 查询，“我想用 LIKE 查询这个字符串”
@search_string = 'I want to query this string with a LIKE'
@search_string = ("%"+@search_string+"%").tr(" ", "%")
@my_query = MyTable.find(:all, :conditions => ['my_column LIKE ?', @search_string])
first I add the '%'to the start and endof string with
首先，我将'%'添加到字符串的开头和结尾
("%"+@search_string+"%")
("%"+@search_string+"%")
and then replace other remaining whitespacechars with '%'like so
然后像这样用'%'替换其他剩余的空白字符
.tr(" ", "%")
.tr(" ", "%")
回答by maialithar
http://www.techonthenet.com/sql/like.php
http://www.techonthenet.com/sql/like.php
The patterns that you can choose from are:
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character
您可以选择的图案有：
% 允许您匹配任何长度的任何字符串（包括零长度）
_ 允许您匹配单个字符
回答by lisandro
I think that the question is not asking to match any spaces but to match two strings one a pattern and the other with wrong number of spaces because of typos.
我认为问题不是要求匹配任何空格，而是匹配两个字符串，一个是模式，另一个是由于拼写错误而导致的空格数错误。
In my case I have to check two fields from different tables one preloaded and the other filled typed by users so sometimes they don't respect 100% the pattern.
在我的情况下，我必须检查来自不同表的两个字段，一个是预加载的，另一个是由用户输入的，因此有时他们不遵守 100% 的模式。
The solution was to use LIKE in the join
解决方案是在连接中使用 LIKE
Select table1.field
from table1
left join table2 on table1.field like('%' + replace(table2.field,' ','%')+'%')
回答by Maddy mokhamatam
if the condition:
如果条件：
WHERE myField LIKE '%Hello world%'
doesn't work try
不行试试
WHERE myField LIKE '%Hello%'
and
和
WHERE myField LIKE '%world%'
this approach is helpful in a few specific use cases, hope this helps.
这种方法在一些特定用例中很有帮助，希望能有所帮助。
