Replace all characters except ascii 32 to 127 and ascii 0, 13, 27 in postgres sql

Question

Is there any function that can replace all characters except ascii 32 to 127 and ascii 0, 13, 27 in postgres sql. I do not want to replace spaces, line channels, etc. I want to replace weird characters like club signs, square or weird stars.

I tried changing regexp_replace as below, but it does not work.

select regexp_replace('abc$wanto&tore9046move#special~04 chars', '[^\x00-\x7f]', '', 'g') 
--This is giving error ERROR: 22021: invalid byte sequence for encoding "UTF8": 0x00

select *, regexp_replace('abc$wanto&tore9046move#special~04 chars', '[^[:ascii:]]', '', 'g')
--This one is taking everything beyond 255 also in the set. 

Thanks so much for your time and help.

Answer 1

Try using the Unicode range:

select regexp_replace('abc$wanto&tore9046move#special~04 chars', '[\u0080-\u00ff]', '', 'g')

Link

This will remove any character in the 128-255ascii range .

Answer 2

You were almost right:

select regexp_replace('abc$wanto&tore9046move#special~04 chars', '[^\x00-\x7f]', '', 'g') 

\x00 PostgreSQL, \x01. 32 (0x20), 13 (0x0d) 27 (0x1b):

select regexp_replace('abc$wanto&tore9046move#special~04 chars', '[^\x20-\x7f\x0d\x1b]', '', 'g')

, :

regress=> select regexp_replace('aáiï*∞ıb ', '[^\x20-\x7f\x0d\x1b]', '', 'g');
 regexp_replace 
----------------
 ai*b 
(1 row)