How to remove specials characters, but replace spaces?

Question

I have a string with spaces & special characters: ABC%$! DEF*& GHI

I want to remove all the specials characters, but replace the spaces with an underscore: ABC_DEF_GHI

I have:

REGEXP_REPLACE(c.category_name, '[^\w]+','','g')

But that removes all the characters, including spaces.

Answer 1

Do the space-to-underscore replace() first, and your existing expression will do the rest.

testdb=# select regexp_replace(replace('ABC%$! DEF*& GHI', ' ', '_'), '[^\w]+','','g');
 regexp_replace 
----------------
 ABC_DEF_GHI
(1 row)

Answer 2

If there is a short, static list of possible special characters, translate() is typically much faster:

test=> SELECT translate('ABC%$! DEF*& GHI', ' %$!*&', '_');
  translate  
-------------
 ABC_DEF_GHI

Every listed character with no replacement is removed.
Else, consider Adam's answer.

But I suppose you wouldn't want leading or trailing _ in the result? May surface after removing noise characters. So feed the result to trim():

test=> SELECT translate('ABC%$! DEF*& GHI %', ' %$!*&', '_')
test-> , trim(translate('ABC%$! DEF*& GHI %', ' %$!*&', '_'), '_');
  translate   |    btrim    
--------------+-------------
 ABC_DEF_GHI_ | ABC_DEF_GHI