Different functions and operators in Postgres use one of three different pattern matching languages, as described in a dedicated section of the manual.
The % form you are using here is the SQL LIKE syntax, where % represents "any number of any character". But the function you are using, regexp_replace, expects a Posix regular expression, where the equivalent would be .* (. meaning any character, * meaning repeat zero or more times).
Also note that LIKE expressions have to match the whole string, but a Posix regex doesn't, unless you explicitly match the start of the string with ^ and the end with $.
So the direct translation of '%''%' would be '^.*''.*$', giving you this:
UPDATE tblName SET colName = regexp_replace(colName, '^.*''.*$', '');
In practice, this would give the same effect as the simpler:
UPDATE tblName SET colname='' WHERE colname LIKE '%''%';
Your actual use case is much simpler: you want to replace all occurrences of a fixed string (', which will need to be quoted and escaped as '''') with another fixed string (the empty string, written ''). So you don't need any pattern matching at all, just straight replacement using replace:
UPDATE tblName SET colname=replace(colname, '''', '');
This will probably be faster if you limit it to rows that contain an apostrophe to begin with:
UPDATE tblName SET colname=replace(colname, '''', '') WHERE colname LIKE '%''%';
