I am learning basics of SQL through W3School and during understanding basics of wildcards I went through the following query:
我正在通过W3School学习SQL基础知识，在理解通配符基础知识的过程中，我进行了以下查询:
--Finds any values that start with "a" and are at least 3 characters in length
WHERE CustomerName LIKE 'a_%_%'
as per the example following query will search the table where CustomerName column start with 'a' and have at least 3 characters in length.
根据下面的查询示例，将搜索CustomerName列以“a”开头、长度至少为3个字符的表。
However, I try the following query also:
不过，我也尝试了以下查询:
WHERE CustomerName LIKE 'a__%'
The above query also gives me the exact same result. I want to know whether there is any difference in both queries? Does the second query produce a different output in some specific scenario? If yes what will be that scenario?
上面的查询也给出了完全相同的结果。我想知道这两个查询是否有什么不同?第二个查询在某些特定场景中产生不同的输出吗?如果是，会是什么情况?
2 个解决方案
#1
9
Both start with A, and end with %. In the middle part, the first says "one char, then between zero and many chars, then one char", while the second one says "one char, then one char".
都以A开头，以%结尾。在中间部分，第一个表示“一个char，然后在0到多个chars之间，然后是一个char”，而第二个表示“一个char，然后是一个char”。
Considering that the part that comes after them (the final part) is %, which means "between zero and many chars", I can only see both clauses as identical, as they both essentially just want a string starting with A then at least two following characters. Perhaps if there were at least some limitations on what characters were allowed by the _, then maybe they could have been different.
考虑到它们后面的部分(最后的部分)是%，这意味着“在0和许多字符之间”，我只能看到这两个子句是相同的，因为它们本质上都只想要一个以a开头的字符串，后面至少有两个字符。也许如果_至少有一些限制字符被允许，那么他们可能是不同的。
If I had to choose, I'd go with the second one for being more intuitive. After all, many other masks (e.g. a%%%%%%_%%_%%%%%) will yield the same effect, but why the weird complexity?
如果我必须选择，我会选择第二种，因为它更直观。毕竟，许多其他的遮罩(比如%%%%%% %%%%%% %%%%%% %%%%%% %%%%%% %%%)都会产生同样的效果，但是为什么奇怪的复杂性呢?
#2
4
For Like operator a single underscore "_" means, any single character, so if you put One underscore like
对于Like操作符，一个下划线“_”意味着，任何一个字符，所以如果你加一个下划线。
ColumnName LIKE 'a_%'
ColumnName像“现代%”
you basically saying you need a string where first letter is 'a' then followed by another single character and then followed by anything or nothing.
你基本上说你需要一个字符串，第一个字母是“a”然后是另一个字符，然后是任何东西或什么都没有。
ColumnName LIKE 'a__%' OR ColumnName LIKE 'a_%_%'
像" a__% "或ColumnName " a_%_% "
Both expressions mean first letter 'a' then followed by two characters and then followed by anything or nothing. Or in simple English any string with 3 or more character starting with a.
这两个短语的意思都是第一个字母“a”，后面跟着两个字符，后面跟着任何东西或什么都没有。或者在简单的英语中，任何以a开头的字符为3或3以上的字符串。
#1
9
Both start with A, and end with %. In the middle part, the first says "one char, then between zero and many chars, then one char", while the second one says "one char, then one char".
都以A开头，以%结尾。在中间部分，第一个表示“一个char，然后在0到多个chars之间，然后是一个char”，而第二个表示“一个char，然后是一个char”。
Considering that the part that comes after them (the final part) is %, which means "between zero and many chars", I can only see both clauses as identical, as they both essentially just want a string starting with A then at least two following characters. Perhaps if there were at least some limitations on what characters were allowed by the _, then maybe they could have been different.
考虑到它们后面的部分(最后的部分)是%，这意味着“在0和许多字符之间”，我只能看到这两个子句是相同的，因为它们本质上都只想要一个以a开头的字符串，后面至少有两个字符。也许如果_至少有一些限制字符被允许，那么他们可能是不同的。
If I had to choose, I'd go with the second one for being more intuitive. After all, many other masks (e.g. a%%%%%%_%%_%%%%%) will yield the same effect, but why the weird complexity?
如果我必须选择，我会选择第二种，因为它更直观。毕竟，许多其他的遮罩(比如%%%%%% %%%%%% %%%%%% %%%%%% %%%%%% %%%)都会产生同样的效果，但是为什么奇怪的复杂性呢?
#2
4
For Like operator a single underscore "_" means, any single character, so if you put One underscore like
对于Like操作符，一个下划线“_”意味着，任何一个字符，所以如果你加一个下划线。
ColumnName LIKE 'a_%'
ColumnName像“现代%”
you basically saying you need a string where first letter is 'a' then followed by another single character and then followed by anything or nothing.
你基本上说你需要一个字符串，第一个字母是“a”然后是另一个字符，然后是任何东西或什么都没有。
ColumnName LIKE 'a__%' OR ColumnName LIKE 'a_%_%'
像" a__% "或ColumnName " a_%_% "
Both expressions mean first letter 'a' then followed by two characters and then followed by anything or nothing. Or in simple English any string with 3 or more character starting with a.
这两个短语的意思都是第一个字母“a”，后面跟着两个字符，后面跟着任何东西或什么都没有。或者在简单的英语中，任何以a开头的字符为3或3以上的字符串。