Thread: Regex

Hi All,

Is there a way to execute a regex, inside a SELECT
statement, to substitute things?

For example:

SELECT name FROM mytable;
 name
---------
 john
 michael
 robert
 richard
 chandler

I want to substitute all "r" to "-"

SELECT myfunction(name) FROM mytable;
 name
---------
 john
 michael
 -obert
 -icha-d
 chandle-

Any idea??

Thanks in advance,
Regards,

Marcelo P.
Brazil



_______________________________________________________
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora!
http://br.acesso.yahoo.com

MaRCeLO PeReiRA <gandalf_mp@yahoo.com.br> writes:
> Is there a way to execute a regex, inside a SELECT
> statement, to substitute things?

There's a regex_replace() function in recent PG versions.  Or you could
write a function in plperl or pltcl to use the regex capabilities of
those languages.

            regards, tom lane

If your version does not support regexp_replace(), I have written a
similar function for easlier versions of postgresql using pl/pgsql
called regexp_replacex(). You can find it by searching google groups.
As the thread there points out, the function I wrote doesn't treat
NULLs properly as posted and there are probably other issues as well.
You are free to use it anyways at your own risk.

If your version of PostgreSQL does support the regexp_replace()
function then you should use it instead as Tom Lane pointed out.

-Robert