Search code examples
sqlregexpostgresqlgreenplum

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

Is there any function which 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 feeds etc. I want to replace weird characters like club signs, square or a weird asterisk.

I tried modifying regexp_replace like below but it is not working. 

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 much for your time and help

Solution

  • Try unicode range:

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

    Reference

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