Today I want to mention the ”LIKE” wildcards.
Many people don’t seem to have realized/read that you can in fact use a regular expression type wildcard. Of course it is nowhere near regular expressions, but it is handy for various situations. The syntax can also be read in the msdn documentation (which incidently is where I fell over it some years ago).

Basically you have the standard wildcards; % and _
However you also have the range wildcard [] and [^] which works very similar to regular expressions.
This means that if I do a LIKE '[abc]%' it’ll match up against conditions where a word starts with a, b or c. This could also be written as '[a-c]%'.
To negate the effect you add a ^ character in the beginning of the string, like this '[^abc]%' which means any word which doesn’t begin with a, b or c.
These wildcards can also be used in for example the PATINDEX like.

This is very handy for many situations, if one remembers them.