Re: PostgreSQL equivelant of this MySQL query

On Jul 13, 2007, at 6:39 PM, Madison Kelly wrote:

> Hi all,
>
> I am reading through some docs on switching to Postfix with a SQL
> backend. The docs use MySQL but I want to use PgSQL so I am trying
> to adapt as I go. I am stuck though; can anyone help give me the
> PgSQL equiv. of:
>
> SELECT CONCAT(SUBSTRING_INDEX(usr_email,'@',-1),'/',SUBSTRING_INDEX
> (usr_email,'@',1),'/') FROM users WHERE usr_id=1;
>
> If the 'usr_email' value is 'person(at)domain(dot)com' this should
> return 'domain.com/person'.

A direct conversion would be something like:

select split_part(usr_email, '@', 2) || '/' || split_part(usr_email,
'@', 1) from users where usr_id=1;

You could also do this:

select regexp_replace(usr_email, '(.*)@(.*)', '\2/\1') from users
where usr_id=1;

http://www.postgresql.org/docs/8.2/static/functions-string.html and
http://www.postgresql.org/docs/8.2/static/functions-matching.html are
the bits of the docs that cover these functions.

Cheers,
Steve