On Sun, Jun 18, 2006 at 02:06:28AM +1000, Mark Constable wrote:
> When using MySQL I have one select that uses MySQL functions so I am
> wondering about the best way to go to end up with the same result in
> PostgreSQL.
You might be interested in the MySQL Compatibility Functions:
http://pgfoundry.org/projects/mysqlcompat/
> uid is an email address stored in the passwd table as user@domain.com
> and this construct allows an incoming username such as "user.domain.com"
> to be compared to the stored "user@domain.com".
>
> SELECT wpath FROM passwd WHERE uid="\L" OR insert(uid,instr(uid,'@'),1,'.')="\L"
The compatibility functions mentioned above can do this. See also
"String Functions and Operators" in the documentation:
http://www.postgresql.org/docs/8.1/interactive/functions-string.html
Example:
overlay(uid PLACING '.' FROM position('@' IN uid) FOR 1)
In 8.1 you could use regexp_replace:
regexp_replace(uid, '@', '.')
For more complex searching and/or replacing you could write a
function in PL/Perl, PL/Python, etc.
--
Michael Fuhr