Re: PostgreSQL equivelant of this MySQL query

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

Thanks Steve!

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

Madi