Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, provided an index on the field column exists.
Prefer = to LIKE
And therefore this advice is wrong. As long as your LIKE expression doesn't start with a wildcard, LIKE can use an index just fine.
Filter with WHERE before HAVING
This usually isn't an issue, because the search terms you would use under HAVING can't be used in the WHERE clause. But yes, the other way around is possible, so the rule of thumb is: if the condition can be evaluated in the WHERE clause, it should be.
WITH
Be aware that not all database engines perform predicate propagation across CTE boundaries. That is, a query like this:
WITH allRows AS (
SELECT id,
result = difficult_calculation(col)
FROM table)
SELECT result
FROM allRows
WHERE id = 15;