Thread: Converting from MySQL

Hello,

I am trying to install a mail server using PostgreSQL on a Debian 9 Stretch.
Document I am following is for MySQL. I converted other simple SQL commands.
Failed to find PostgreSQL equivalent for below one because I did not
understand what it does in the first place.

SELECT
CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
FROM users WHERE email='%s'

Any help is appreciated.

Thanks & regards,
Ertan Küçükoğlu

On Tue, Jan 22, 2019 at 2:21 PM Ertan Küçükoğlu
<ertan.kucukoglu@1nar.com.tr> wrote:
> Failed to find PostgreSQL equivalent for below one because I did not
> understand what it does in the first place.
>
> SELECT
> CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
> FROM users WHERE email='%s'

PostgreSQL doesn't have a function named substring_index (it does have
concat, and a concat_ws variant, plus the || operator)

I believe the following demonstrates equivalent functionality for the
substring_index function - you could turn it into function of the same
name if you so choose.

select split_part(v, '@', 1), right(v, -(length(split_part(v, '@', 1))+1))
from ( values ('abc@123'), ('abc@123@%%%') ) vals (v)

The MySQL documentation explains what substring_index is doing - in
this case you need to get the "left and right side" components
separately via PostgreSQL functions for which I choose split_part and
right.  A similar result can be had via regular expressions in a more
succinct (though not necessarily faster) way; or less succinctly via
substring.

If it wasn't for the possibility for having multiple "@" in an email
address split_part(,1) and split_part(,2) would be sufficient by
itself.

David J.

 >> Failed to find PostgreSQL equivalent for below one because I did not
 >> understand what it does in the first place.
 >> 
 >> SELECT
 >> CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
 >> FROM users WHERE email='%s'

It looks to me like this would work as well or better in PG:

select regexp_replace(email, '^(.*)@(.*)$', '\2/\1/') from ...

-- 
Andrew (irc:RhodiumToad)