PostgreSQL Removing control characters from a string

1

PostgreSQL

We have some bytea columns that contain various control characters including nulls.

How can I remove them?

replace(column,CHR(0)," ") comes back with ERROR: null character not permitted.

regexp_replace(column::test, ‘[^ [:alnum:]]’,’ ',‘g’) doesn’t do anything.

Any ideas?

2

Refering to the postgres reference documentation …

Look at the regular expression functions in postgres:
replace (text, text, text )
translate (text, text, text )

regexp_instr(string, pattern)
regexp_substr(string, pattern)
regexp_replace(string, pattern, replacement)

Example:
SELECT regexp_replace(
E'Hello\x00World\x00!',
E'\x00',
'',
'g'
);