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?
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'.