regex - How to extract the domain from an email in a string using PostgreSQL?

I'm working with a PostgreSQL database where email addresses are stored in a string along with names, like this:

Lucky kurniawan <[email protected]>

I need to extract only the domain (e.g., hotmail).

I'm working with a PostgreSQL database where email addresses are stored in a string along with names, like this:

Lucky kurniawan <[email protected]>

I need to extract only the domain (e.g., hotmail).

Share Improve this question asked Apr 1 at 10:12 lucky kurniawan 1362 silver badges8 bronze badges

select split_part('[email protected]', '@', 2); – Mike Organek Commented Apr 1 at 12:09

Add a comment |

2 Answers 2

Sorted by: Reset to default 0

I attempted to use substring() with regex but haven't found the best approach.

Here’s an SQL query that partially works:

SELECT substring(email_from FROM '<[^@]+@([^>]+)>') AS domain
FROM my_table;

For the input Lucky kurniawan <[email protected]>, it correctly returns:

hotmail

SELECT 
    substring(email_from FROM '.*<([^@]+@[^>]+)>') AS domain
FROM 
    my_table;

It will match any character before the "<" making it more flexible.

It also captures the full email address inside the < > and then then extracts the domain.