Обсуждение: 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 >