Why does SSMS highlight SUBSTRING in purple but RIGHT in grey? I apologise for the trivial question, but I was trying to work out how to do a RIGHT in SQL so I experimentally typed RIGHT and it didn't highlight in purple, so I spent some time scratching my head about how to do this using substring and length, then I later discovered that RIGHT is perfectly valid.

How come it wasn't highlighted purple?
Because while you're right that RIGHT() is a function, RIGHT is also a keyword, as in RIGHT OUTER JOIN. They have to pick one (SSMS is not smart enough to detect context). I face the same problem in the syntax highlighting plugin I use for my blogs - it either has to color all instances of RIGHT as grey, or all instances of RIGHT as purple.

(Also, if you have doubts about T-SQL syntax, don't waste your time trying to rely on IntelliSense to guide you - consult the documentation, that's what it's for.)