Question 89597 in PostgreSQL Question posted in
The official documentation can be found here.

Split multiple email address and output to 3 columns (username, domain, extension) in Postgresql

I am new to Postgres and would like to learn how to split multiple email address at once and the final table ouput would have 3 columns username, domain, extension.

For example I have multiple email address.

[email protected] and so forth

I need an output on the table like this.

username domain extension
robert bryne gmail com

Also, how do I insert into with all of this select results in my table.

Tags:

2

Answers


  1. 0

    You could try to use REGEXP_MATCHES to match email address, then retrieve each email part in matched result.

    WITH data AS 
    (SELECT '[email protected]' AS email, 
     REGEXP_MATCHES('[email protected]', 
       '^(w+[.-]?w+)*@(w+[.-]?w+)*.(w{2,3})+$', 'g') AS email_parts)
SELECT REPLACE(email_parts[1], '.', ' ') AS username,  
       email_parts[2] AS domain,
       email_parts[3] AS extension
FROM data;

    Demo

    Login or Signup to reply.
  2. 0

    You can use split_part

    select split_part(email, '@', 1) as username, 
       split_part(split_part(email, '@', 2), '.', 1) as domain, 
       split_part(split_part(email, '@', 2), '.', 2) as extension
from the_table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search