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.