Closed. This question is not reproducible or was caused by typos . It is not currently accepting answers.
This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.
Closed 20 hours ago.
Improve this question
I discovered a problem on my procedure and finally the problem was found. When trying to ask documents with configuration name 'Default' these two queries give two different results:
SELECT DC.ConfigurationName, DC.ConfigurationID
FROM DocumentConfiguration DC
WHERE DC.ConfigurationName='Default'Results:
| ConfigurationName | ConfigurationID |
|---|---|
| Default | 1 |
| Default | 2 |
That lower 'Default' has a trailing space, ASCII character 32.
This query gives only the exact result I needed:
SELECT DC.ConfigurationName, DC.ConfigurationID
FROM DocumentConfiguration DC
WHERE DC.ConfigurationName LIKE 'Default'Results:
| ConfigurationName | ConfigurationID |
|---|---|
| Default | 1 |
I understood = should give only the exact match while LIKE should be used with wildcards. Why those two queries seem to work the opposite of how they should?
I fixed the procedure so that it now uses LIKE instead of = and the whole procedure works correctly. I did not expect this to work, because = should only give an exact match.
2条答案
按热度按时间fcg9iug31#
Likely you have trailing spaces on the end of one of the values. When this happens, the
=operator treats them both as matches, and thelikeoperator does not, per ANSI standards according the docs.More info here: https://learn.microsoft.com/en-US/sql/t-sql/language-elements/string-comparison-assignment?view=sql-server-ver16#remarks
The SQL Server Database Engine follows the ANSI/ISO SQL-92 specification (Section 8.2, Comparison Predicate, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations. The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, the Database Engine doesn't pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this predicate doesn't violate the section of the ANSI SQL-92 specification mentioned earlier.
The table column data type (
nvarcharvsvarchar) also impacts the results when using theLIKEoperator (tested in SQL 2014 and SQL 2022).Below are results of a test query with various filters using
nvarcharvsvarchar, where each row represent the results for the where clause applied against a table with 'Default' and 'Default ' values.Notice how test case "3. nvarchar like 'Default'" (row 5) only returns one match, but test case "7. varchar like 'Default'" (rows 11,12) returns 2 rows. Test case 3 is the same filter as test case 7 but just different column data types.
As a side note, the
lenfunction also ignores trailing spaces, so 'Default ' with a space on the end returns a len of 7 instead of 8. Thedatalengthfunction does include trailing spaces.*the square brackets in the testval results above were only added to the query results to clearly show which testval had the space on the end, but the raw data does not have the square brackets.
vsdwdz232#
According to section 8.2, General Rules, paragraph 3.a. in the ANSI standard :
If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS.
Therefore, what you see is the correct result for the equality comparison, since the string without the trailing space is padded for the comparison to match the string with the space.
The documented behavior for
LIKEis defined in section 8.5, but it's much more complicated and harder to infer results for individual comparisons. The gist of it is theLIKEoperator is also used for matching substrings within larger strings, and so it doesn't work to define the padding described in 8.2/GR/3aAgain, it's complicated, but the easiest place to understand it is 8.5, Generals rules, Part 5. This has sections
a),b), andc), where everything in eithera)orb)must be true, or you getc):Otherwise, M LIKE P is false.
In this case,
a)only holds if both sides are 0 length, so clearly not true, and we find this in 5.b.v:[The predicate is true if there exists a partitioning of M into substrings such that:...] the number of substrings of M is equal to the number of substring specifiers of P.
But since you can take a substring to include the last trailing space from one side of the expression and not the other, this check cannot succeed for those strings.
Of course, this is the 92 standard, which is quite old now. However, later standards are not as easy to reference publicly* and the behaviors are similar.
Therefore we see the result for the
LIKEcomparison in the question also follows the standard.