Hugo Kornelis (12/19/2011)
That quote only reinforces my expectation that the third query should return 1. Can anyone explain to me why it doesn't?
It is 'explained' (perhaps 'documented' would be a better term) in the last paragraph of Pattern Matching in Search Conditions
And, while you're at it, you might also want to explain the even stranger behaviour demonstrated by the query Paul posted in this topic).
This point needs LIKE (Transact-SQL) in the sub-section 'Pattern Matching by Using LIKE'. Hilarious...
DECLARE @Example TABLE (col1 VARCHAR(10))
INSERT @Example VALUES ('X'), ('X' + SPACE(1)), ('X' + SPACE(2))
DECLARE @ESC1 CHAR = '['
DECLARE @ESC2 NCHAR = '['
SELECT COUNT_BIG(*) FROM @Example AS e WHERE col1 LIKE 'X' + SPACE(1) ESCAPE @Esc1
SELECT COUNT_BIG(*) FROM @Example AS e WHERE col1 LIKE 'X' + SPACE(1) ESCAPE @Esc2
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi