Skip site navigation (1) Skip section navigation (2)

Re: String Translation

From: elein <elein(at)varlena(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>
Cc: elein <elein(at)varlena(dot)com>, sfpug(at)postgresql(dot)org
Subject: Re: String Translation
Date: 2003-10-16 18:19:35
Message-ID: 20031016111935.E21407@cookie.varlena.com (view raw or flat)
Thread:
Lists: sfpug
Bummer. You would get a *lot* of speed in a lot of places
if you were able to run plperl functions.

You can also use the translate(string::text, charset::text, replaceset::text) function. 
It is kind of ugly in this case.  From the docs:

   Any character in string that matches a character in
   the from set is replaced by the corresponding character in the to  set. 
   translate('12345', '14', 'ax')a23x5


translate('AbCdEfG','0123456789abcdefghijklmnopqrstuvwxyz','____________________________________');

so the query would be 

update table
   set column=
   translate(column, '0123456789abcdefghijklmnopqrstuvwxyz','____________________________________')
   where column=column;


elein

On Thu, Oct 16, 2003 at 10:40:23AM -0700, David Wheeler wrote:
> On Thursday, October 16, 2003, at 10:30  AM, elein wrote:
> 
> >The basic SQL is this:
> >
> >   update <table>
> >      set <column>=fixident(<column>)
> >      where <column> = <column>;
> >
> >If the table were "contrib" and the column in question
> >was "email", do:
> >
> >   update contrib set email=fixident(email) where email=email;
> >
> >And where fixident(text) is the perl function
> >
> >create or replace function fixident (text)
> >returns text as
> >'
> >      my $data = $_[0];
> >      $data =~ y/a-z0-9/_/cs;
> >      return $data;
> >' language 'plperl';
> >
> >This should be faster.  You don't need to fetch
> >the value of the keyname before hand.
> 
> Yes, but unfortunately, I can't expect many Bricolage installs to have 
> PL/perl installed. I don't even have it installed, and I'm a Perl geek!
> 
> Thanks!
> 
> David
> 
> -- 
> David Wheeler                                     AIM: dwTheory
> david(at)kineticode(dot)com                              ICQ: 15726394
> http://www.kineticode.com/                     Yahoo!: dew7e
>                                                Jabber: Theory(at)jabber(dot)org
> Kineticode. Setting knowledge in motion.[sm]

In response to

sfpug by date

Next:From: David WheelerDate: 2003-10-16 23:37:23
Subject: Re: String Translation
Previous:From: David WheelerDate: 2003-10-16 18:03:34
Subject: Re: String Translation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group