PostgreSQL equivelant of this MySQL query

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'.

Thanks for the help!

Madison

Bah, answered my own question after posting. :)

For the record:

SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM
'(.*)@') FROM users WHERE usr_id=1;

Sorry for the line noise!

Madi

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

Steve Atkins wrote:

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

Thanks Steve!

Those look more elegant that what I hobbled together. :)

Madi

select split_part(usr_email,'@',2) ||split_part(usr_email,'@',1)
from ..