Re: MySQL insert() and instr() equiv

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