Extract all the distinct email id domain from all the employee
Question
Suppose there are two columns in employee table i.e. emp id and email
get all the unique domains like gmail.com, yahoo.com, outlook.com, etc.
solved
6
SQL
4 years
18 Answers
2334 views
Grand Master 0
Answers ( 18 )
Sorry it a private answer.
You just have to extract the domain name
abc@gmail.com
ssd@yahoo.com
Answer
gmail.com
yahoo.com
SELECT DISTINCT (SUBSTRING(E.email, 0, (CHARINDEX(‘@’,E.email)+1))) as domain_name
FROM employee E
Select Distinct(Substring(email,(charindex(‘@’,email))+1))
From employee;
SELECT disticnct(substring(‘puja@gmailllllll.com’,CHARINDEX(‘@’, ‘puja@gmailllll.com’)+1,19) )AS MatchPosition;
Good approach, but it’s a bit hard-coding. Try to remove it
SELECT disticnct(substring(Email,,CHARINDEX(‘@’, Email)+1,19) )AS MatchPosition;
How is that?
SELECT distinct split_part(email,’@’,2) as domain FROM employee
Definitely a correct answer..Let’s see if someone can actually come up with something on the lines of index and right function 😛
–Here it is
SELECT distinct
right(email,len(email) – charindex(‘@’,email)) as domain
FROM employee
SELECT distinct substring(EMAIL,charindex(‘@’,email) + 1,LEN(EMAIL) ) from avi
SELECT distinct VALUE from avi
CROSS APPLY STRING_SPLIT(EMAIL, ‘@’)
WHERE VALUE LIKE ‘%com’;
SELECT DISTINCT RIGHT(EMAIL,LEN(substring(EMAIL,charindex(‘@’,email) + 1,LEN(EMAIL) ))) –,RIGHT(EMAIL, substring(EMAIL,0,charindex(‘@’,email)) )
from avi;
Select distinct regexp_replace(email,’.+@(.+)’,’\1′) from employee
Select right(email,len(email)-substr(email,1,Position(@ in email))) from employees
select distinct emailid from emails
where SUBSTRING_INDEX(emailid , ‘@’ , -1);
select substr(email, instr(email, ‘@’) + 1, length(email)) as Domain from emp1;
SELECT DISTINCT SPLIT_PART(email,’@’,2) AS domain_name
FROM employees
select distinct split_part(email,’@’,2)as doman_name
from
employee
SELECT SUBSTR(email, instr(email, ‘@’) +1, length(email)) as domain from Employee