I'm supprised no-one has mentioned another implication of collation ordering and special characters that can be quite important; the impact on LIKE clauses that use set's of characters e.g.
DECLARE @var CHAR(1) = '¾'
SELECT @var
WHERE @var LIKE '%[0-9]%'
Similarly, this applies to the accented characters, so trying to use [a-z0-9] will not yield the standard set of alphanumeric characters that might be expected.
Which given the low success rate on this question (including my own error), I would suspect that this is a common oversight.
So thank you for what is actually an excellent question.