How do I get the first match from regexp_match?

Question

I'd like to update a column with just the domain part of an email address.

update
  users
set
  email_base_domain = regexp_match(email, '(\w+\.\w+)$')

However, regexp_match returns a text[]. If email is example@foo.com the above sets email_base_domain to {foo.com}. I would like foo.com.

How do I get just the first element as text from the text[] returned by regexp_match?

Answer 1

Add a set of parentheses to group what you want to extract. For example:

SELECT regexp_matches(email, '(\w+)\.\w+$')
  FROM users

will return {foo}, while

SELECT regexp_matches(email, '\w+\.(\w+)$')
  FROM users

will return '{com}'.

Once you have a text array you can use regular array indexing to extract values from the returned text array. For example:

SELECT (regexp_matches(email, '(\w+)\.\w+$'))[1]
  FROM users

returns 'foo' and

SELECT (regexp_matches(email, '\w+\.(\w+)$'))[1]
  FROM users

returns 'com'.

db<>fiddle with other alternatives here