Re: MySQL insert() and instr() equiv

Mark Constable <markc(at)renta(dot)net> writes:
> uid is an email address stored in the passwd table as user(at)domain(dot)com
> and this construct allows an incoming username such as "user.domain.com"
> to be compared to the stored "user(at)domain(dot)com".
> SELECT wpath FROM passwd WHERE uid="\L" OR insert(uid,instr(uid,'@'),1,'.')="\L"

Well, if you want to write it exactly that way you'd need to write
insert() and instr() functions, which would probably take about five
minutes in any reasonably decent string-mashing language such as plperl.
You could do it in plpgsql but it'd be more painful. (There's an
example of coding instr() in the back of the plpgsql manual chapter,
but it's intended to match Oracle's version of instr() which might not
be quite like MySQL's.)

But, if you're not wedded to that particular way, why not use replace()?

SELECT wpath FROM passwd WHERE uid="\L" OR replace(uid, '@', '.')="\L"

regards, tom lane