Обсуждение: 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