How to change the name to a name with asterisk when using case

As you may be able to tell from the mangled code highlighting, the string 'Jo'Burg' is not valid. SQL interprets that as the string 'Jo', followed by the unknown operator Burg, and finally an unterminated string '.

To include a single quote in a SQL string, you need to escape it:

CASE
    WHEN AD.[Site] = 'Johannesburg' THEN 'Jo''Burg'

You haven't provided much to go on, but one obvious error is mismatched single quotes:

THEN 'Jo'Burg'

Single quotes denote the start and end of strings, and you've got a single quote in your string Jo'Burg. Your THEN clause is actually this:

THEN 'Jo'

and everything after that is unintelligible garbage to SQL.

To escape the single quote as part of the string, you have to double up the single quote character:

THEN 'Jo''Burg'