agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedConverting from MySQL
3+ messages / 3 participants
[nested] [flat]
* Converting from MySQL
@ 2019-01-22 21:21 =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
0 siblings, 1 reply; 3+ messages in thread
From: Ertan Küçükoðlu @ 2019-01-22 21:21 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Converting from MySQL
@ 2019-01-22 21:42 David G. Johnston <david.g.johnston@gmail.com>
parent: =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2019-01-22 21:42 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Converting from MySQL
@ 2019-01-23 06:38 Andrew Gierth <andrew@tao11.riddles.org.uk>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew Gierth @ 2019-01-23 06:38 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>; David G. Johnston <david.g.johnston@gmail.com>
>> 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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-01-23 06:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-22 21:21 Converting from MySQL =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2019-01-22 21:42 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-23 06:38 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox