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 [EMAIL PROTECTED] > and this construct allows an incoming username such as "user.domain.com" > to be compared to the stored "[EMAIL PROTECTED]". > > 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
