Обсуждение: PostgreSQL equivelant of this MySQL query

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

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

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend