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@xxxxxxxxxx > and this construct allows an incoming username such as "user.domain.com" > to be compared to the stored "user@xxxxxxxxxx". > > 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
