Re: String Translation

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]