Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am trying to select the domain from the column where I have email addresses.
For example, I have a table email_table and a column email with email addresses as below.
[email protected]
[email protected]
[email protected]
Expected result
gmail.com
yahoo.com
gmx.com
Here is the query I tried with CHARINDEX that works in SQL Server but not in DB2.
select CHARINDEX('@', email) from email_table
It's LOCATE (in DB2) which is equivalent to CHARINDEX in SQL Server.
Here is the complete description of it with example.
Thanks!
In Db2 (for LUW) V11.1 and above, you can use REGEXP_EXTRACT
E.g.
SELECT REGEXP_EXTRACT(c,'@(.*)$',1,1,'',1)
FROM TABLE(
VALUES
('[email protected]')
,('[email protected]')
,('[email protected]')
) AS t(c)
returns
1
-----------------
gmail.com
yahoo.com
gmx.com
3 record(s) selected.
