CODEWITHSUNDEEP
regexpostgresqlsubstring
Joshua Rajandiran
Joshua Rajandiran

Reputation: 2928

How to extract the part before the last '@' character of an email in postgresql?

Example email: ex@mpleEm@[email protected]

Expected result: ex@mpleEm@il

How do I do this?

Upvotes: 2

Views: 1554

Answers (2)

IMSoP
IMSoP

Reputation: 97688

The simplest solution is probably to use a POSIX regular expression match:

select substring('ex@mpleEm@[email protected]' from '(.*)@');

There are two slightly subtle things going on here:

  • Under the rules of POSIX regexes, the .* matches as much as possible, which is why it includes the extra @s (compare select substring('ex@mpleEm@[email protected]' from '(.*?)@'); which has a "non-greedy" modifier)
  • The substring function returns the contents of the first capture if there are any capturing parentheses, which is why it doesn't include the final @ (compare select substring('ex@mpleEm@[email protected]' from '.*@'); which has no capturing parentheses)

Upvotes: 3

klin
klin

Reputation: 121524

Use substring(string from pattern):

select substring('ex@mpleEm@[email protected]' from '(.*)@');

  substring   
--------------
 ex@mpleEm@il
(1 row)

Upvotes: 4

Related Questions