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