Postgres - removing a set of letters from a string

Question

I want to remove vowels from an email id. What function should I use? I am trying to find the difference between translate and replace in postgresql but have not received the exact difference

Answer 1

In this case, you probably really want regexp_replace .

Assuming "vowel", you mean "Western European (English) letters of vowel languages", you can write:

 SELECT regexp_replace(' [e-mail] ', '[aeiou]', '', 'gi'); 

gi in the fourth argument says: "Apply this regular expression g to all input lines, not only the first match, but make it case i nsensitive."

Remember that w and y are sometimes vowels, depending on their context. You cannot handle this with a regular expression so that it depends on whether you care for this purpose.

You are less likely to deal with other character sets if you work with email addresses, so a regex might be ok for that.

In most cases, distorting words with regular expressions would not be a good approach; for example, a Russian in Cyrillic uses A like vowels. In addition, depending on the language, the same letter in the same script may or may not be a vowel! Keep reading here for more than you wanted to know .

Answer 2

translate() replaces a set of single characters (passed as a string) with another set of characters (also passed as a string), for example:

 translate('abcdef', 'ace', 'XYZ') --> 'XbYdZf' 

replace() replaces occurrences of a string of arbitrary length with another string:

 replace('abcdef', 'bc', 'FOO') --> 'aFOOdef'