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

admin管理员组

文章数量:1360339

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).

• regex
• string
• postgresql

Share Improve this question Follow asked Apr 1 at 10:12 lucky kurniawanlucky kurniawan 13622 silver badges88 bronze badges 1

• 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.

本文标签： regexHow to extract the domain from an email in a string using PostgreSQLStack Overflow