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@domain.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