0

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.

johndoe@gmail.com
jhefid@yahoo.com
ipw@gmx.com

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
  • INSTR, INSTR2, INSTR4, INSTRB, LOCATE, LOCATE_IN_STRING... select one which is more safe for your task. – Akina Jul 18 at 5:35
2

In Db2 (for LUW) V11.1 and above, you can use REGEXP_EXTRACT

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061492.html

E.g.

SELECT REGEXP_EXTRACT(c,'@(.*)$',1,1,'',1) 
FROM TABLE(
    VALUES
     ('johndoe@gmail.com')
    ,('jhefid@yahoo.com')
    ,('ipw@gmx.com')
) AS t(c)

returns

1                
-----------------
gmail.com        
yahoo.com        
gmx.com          

  3 record(s) selected.
1

It's LOCATE (in DB2) which is equivalent to CHARINDEX in SQL Server.

Here is the complete description of it with example.

Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.