You need to use double quotes to represent single quote for SQL to understand:
For instance, to replace single quote with empty space:
(I have colored red for input quotes and non red for replacements for easy reading)
Replace(@string, '''','')
To replace single quote with two single quotes, it would be
Replace(@string,'''','''''')
Just remember, 2 quotes for each single quote, plus opening and closing quotes.
HTH
