Removing all the vowels from a string - PL/SQL

Question

I have this homework assignment :

 Write an anonymous PL/SQL block that accepts a string as input and removes all of the vowels (a.e.i.o.u) from the string, then outputs the results.

 The output should look like this:

 Run the Program

 SQL>@rm_vowels

 Enter the String: A penny for your thoughts

 SQL>****************************

 SQL>The new string is: pnny fr yr thghts

And this does look like something really easy to do but I'm really lacking some PL/SQL experience to get this done.

From my searches so far I've realized I need to use something similar to this :

REGEXP_REPLACE(name,'[a,e,i,o,u,A,E,I,O,U]','')

Right ?

Answer 1

You can also use the translate function, which might be marginally faster than regexp_replace:

select translate('A penny for your thoughts', 'xaeiouAEIOU', 'x') new_str from dual;

NEW_STR           
------------------
 pnny fr yr thghts

You may wish to put a trim around to remove any leading/trailing spaces.