Today's question (by Steve Jones - SSC Editor):

Join Hint Limitations

SELECT p.Name, pr.ProductReviewID  
FROM Production.Product AS p  
INNER REMOTE JOIN SQL02.Inventory.Production.ProductReview AS pr  
ON p.ProductID = pr.ProductID  
ORDER BY ProductReviewID DESC
COLLATE Traditional_Spanish_cs_as ASC;

Think you know the answer? Click here, and find out if you are right.

LIKE Condition for Date

Which lines will I get back from the following statements:

IF '2024_03_04' LIKE '[12][8901][0-9][0-9][ _-,.]%' SELECT 'underscore_1'
IF '2024_03_04' LIKE '[12][8901][0-9][0-9][ _,.-]%' SELECT 'underscore_2'
IF '2024-03-04' LIKE '[12][8901][0-9][0-9][ _-,.]%' SELECT 'dash_1'
IF '2024-03-04' LIKE '[12][8901][0-9][0-9][ _,.-]%' SELECT 'dash_2'

Answer: only underscore_2 and dash_2

Explanation: When you use [ _-,.] in the LIKE condition as in the underscore_1 / dash_1 statement, the SQL server interprets it as "any char that is either a space, between underscore and comma or a dot", because the dash is a protected char when used in square brackets inside a LIKE condition. Since the underscore has the ASCII code 95 and the comma is 45 there are no numbers between, so both comparisions fail and underscore_1 / dash_1 will never be returned When you put the dash at the end (just before the closing ]) of this part of the LIKE condition as in underscore_2 / dash_2 SQL server is smart enough to recognize that this dash can't  be a BETWEEN and uses it as valid char option and the interpretation will become "any char that is either a space, underscore, comma, dot or a dash". However to prevent mistakes regarding this undocumented "feature" (on the long term it is almost guaranteed that someone needs to add another char to the end of the list reintroducing this "bug") it is much better explicit escape the dash and use something as

IF '2024-03-30' LIKE '[12][8901][0-9][0-9][ _/-,.]%' ESCAPE '/' SELECT 'dash_escaped' -- I wanted to use the common backslash here, but it gets removed by Wordpress. 
-- In your code you could of course use backslash instead of slash or any other char that should not be occur in your string
IF '2024-03-30' LIKE '[12][8901][0-9][0-9][ _µ-,.]%' ESCAPE 'µ' SELECT 'dash_escaped'

Ref: Wildcard - characters to match - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/wildcard-character-s-to-match-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.