Using the REPLACE command to replace all vowels

On 1 May 2006 13:16:15 -0700, EbGrooveCb <cbaumann@cmsgrp.com> wrote:

Is there a way to make seperate replacements in 1 field in one command
in SQL?

I need to remove all vowels (a,e,i,o,u) in a field. How would I go
about that?

You can do all the replacements in a single SQL statement by nesting
the REPLACE statements. This will just strip those letters.

SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
'Your string or field here.'
,'a','')
,'e','')
,'i','')
,'o','')
,'u','')
;

Is there a way to make seperate replacements in 1 field in
one command in SQL?

I need to remove all vowels (a,e,i,o,u) in a field. How would
I go about that?

Try something like
SELECT regexp_replace(your_string,'[aeiou]','','g') ...

(btw, if you want all vowels, don't forget 'y' :-P)

//Magnus

On Wed, May 03, 2006 at 09:38:47AM +0200, Magnus Hagander wrote:

SELECT regexp_replace(your_string,'[aeiou]','','g') ...

I'll be darned. I've been looking for that function. I expected to
find it in the docs under "String Functions" with the other replace
functions. I'm surprised to find it under "Pattern Matching."

Magnus Hagander wrote:

Is there a way to make seperate replacements in 1 field in
one command in SQL?

I need to remove all vowels (a,e,i,o,u) in a field. How would
I go about that?

Try something like
SELECT regexp_replace(your_string,'[aeiou]','','g') ...

(btw, if you want all vowels, don't forget 'y' :-P)

As I recall, that should be 'and sometimes y... I don't recall the sql
syntax for SOMETIMES :)

--
Until later, Geoffrey

Any society that would give up a little liberty to gain a little
security will deserve neither and lose both. - Benjamin Franklin

Is there any requirement that you have to use REPLACE? Another (possibly
better) way to do the same is by doing:

select translate(your_string,'aeiou','') from your_table;

In my experience that performs much better than regexp. I just ran it on
a table of about 100K random U.S addresses and TRANSLATE outperforms
REGEXP_REPLACE by a factor of 2. I would use TRANSLATE unless you need
some of the heavier lifting that REGEXP can provide.