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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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]
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2003-10-16 23:37:23 | Re: String Translation |
Previous Message | David Wheeler | 2003-10-16 18:03:34 | Re: String Translation |