Re: Converting from MySQL

On Tue, Jan 22, 2019 at 2:21 PM Ertan Küçükoğlu
<[email protected]> 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.