postgres regexp_replace want to allow only a-z and A-Z

In a table column in string we can have numbers/special chars/white spaces. I want to replace numbers/special chars/white space with empty char, i see there is function named regexp_replace but how to use not much user friendly help avaialble for example i want to use followin开发者_开发知识库g string.

String = 'abc$wanto&toremove#special~chars'

I want to remove all special chars and numbers from above string want to allow only a-z and A-Z rest of chars should be replaced with '' how to do that ?

SELECT regexp_replace('abc$wanto&toremove#special~chars', '[^a-zA-Z]', '', 'g');

        regexp_replace        
------------------------------
 abcwantotoremovespecialchars

For me the following worked.

regexp_replace(code, '[^a-zA-Z0-9]+', '','g')    

As it adds global filter so it repeats the regex for the entire string.

Example,

SELECT regexp_replace('Well- This Did-Not work&*($%%)_', '[^a-zA-Z0-9]+', '')    

Returns: "WellThis Did-Not work&*($%%)_"

SELECT regexp_replace('Well- This Did-Not work&*($%%)_', '[^a-zA-Z0-9]+', '','g')    

Returns: "WellThisDidNotwork"

Which has the characters we don't want removed.

To make it simpler:

regexp_replace('abc$wanto&toremove#special~chars', '[^[:alpha:]]')

If you want to replace the char with the closest not special char, you can do something like this:

select
  translate(
    lower( name ), ' ''àáâãäéèëêíìïîóòõöôúùüûçÇ', '--aaaaaeeeeiiiiooooouuuucc'
  ) as new_name,
  name
from cities;

Should be:

regexp_replace('abc$wanto&toremove#special~chars', '[^a-zA-Z]+', '')