Обсуждение: Using the REPLACE command to replace all vowels

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?

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.



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Geoffrey
> Sent: Sunday, May 07, 2006 3:54 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using the REPLACE command to replace all vowels
>
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>