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!!
Try the below. I hope it will give you expected result
sel emialtext,substr(emailtext,index(emailtext,'.')) from sch.table where emailtext like '%.%'
Dneoth i have one question
I have 2 strings
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)
I Have another issue again i have 2 ids & i need to join then
id 1 = 10126409.1
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?