3

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

2 Answers 2

3

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

Here is the complete description of it with example.

Thanks!

3

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
     ('[email protected]')
    ,('[email protected]')
    ,('[email protected]')
) AS t(c)

returns

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

  3 record(s) selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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