Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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;