Removing a set of letters from a string

Question

I want to remove the vowels from the email id. Which function should I use? I am trying to find the difference between translate and replace in postgresql but didn't get the exact difference

Answer 1

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'

Answer 2

In this case you probably actually want regexp_replace.

Assuming by "vowel" you mean "Western European (English) language vowel letters" you might write:

SELECT regexp_replace('[email protected]', '[aeiou]', '', 'gi');

the gi in the fourth argument says "apply this regular expression globally to the whole input string not just to the first match, and make it case insensitive".

Remember that w and y are sometimes vowel-sounds, depending on their context, too. You won't be able to handle that with a regexp so it depends on whether or not you care for this purpose.

You're less likely to need to deal with other character sets if you're working with email addresses so a regexp might be OK for this.

In most cases mangling words with regular expressions would not be a good approach, though; for example, Russian in the Cyrillic alphabet uses A Э У О Ы Я Е Ё Ю И as vowels. Additionally, depending on the language, the same letter in the same script might or might not be a vowel! Keep reading here for more than you ever wanted to know.