Extracting Domain Names from EMAIL_TXT Field

Database

Extracting Domain Names from EMAIL_TXT Field

I am new to TD and am attempting to pull the domain names (.com, .net, .ca) from email address.    SELECT COUNT(DISTINCT(email_txt)) FROM sch.table WHERE email_txt  LIKE  ('%us');  An issue I run into is for the domains that I do not know.  How to use SELECT TRIM or SELECT SUBSTR to count and group the ones that are not known?  Another issue is some are 3 CHARS like .com and some being 2 CHARS like .ca  Any thoughts?  Help is appreciated.  Cheers!!

7 REPLIES
Junior Contributor

Re: Extracting Domain Names from EMAIL_TXT Field

You might try:

trim(leading '.' from substring(email_address from char_length(x) -2))

Dieter

Enthusiast

Re: Extracting Domain Names from EMAIL_TXT Field

Hi,

Try the below. I hope it will give you expected result

sel emialtext,substr(emailtext,index(emailtext,'.')) from sch.table where emailtext like '%.%'

Venkat

Teradata Employee

Re: Extracting Domain Names from EMAIL_TXT Field

Dneoth i have one question

I have 2 strings

10371676.2.1

10374263.14.1

 I need to substr it from first position till the second dot if i substr(id,1,11) then its going wrong coz if the id is like 10374263.144.1 then the case will go wrong plus  substr will fail the join in this 10371676.2.1 too can u please suggest me the solution

Junior Contributor

Re: Extracting Domain Names from EMAIL_TXT Field

Wha's your TD release?

In TD14 there's a built-in INSTR to find the #2 dot for the SUBSTRING or REGEXP_SUBSTR, before that you might check what UDFs have been installed.

Otherwise it's ugly nesting of POSITIONTSUBSTRING:

SUBSTRING(x FROM 1 FOR POSITION('.' IN x) + POSITION('.' IN SUBSTRING(x FROM POSITION('.' IN x)+1)) -1)

Dieter

Teradata Employee

Re: Extracting Domain Names from EMAIL_TXT Field

TD 14 thanx alot it worked (Y)

Teradata Employee

Re: Extracting Domain Names from EMAIL_TXT Field

I Have another issue again i have 2 ids & i need to join then

id 1 = 10126409.1

another example of id 1 is  10126409.13

and 

ID 2 = 10037765.1.

Another example of id 2 is 10769022.11.

how can i join them if i use the above suggested query its not goin to join on all ids.

Enthusiast

Re: Extracting Domain Names from EMAIL_TXT Field

Seems like these are just two decimal values which you are joining upon. Why do you think a simple join with equal sign won't work?