Extract domain name from email id
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 7 )
‘xyg@domain.com’.split(“@”)[1].split(“.”)[0]
output->domain
Where email is the column containing email id’s in the form of ‘abc@domain.com’ and domain is the table name (PGSQL)
SELECT email,split_part(split_part(email,’@’,2),’.’,1)
FROM domain;
SELECT ID,
SUBSTRING ([Email], CHARINDEX( ‘@’, [Email]) + 1,
LEN([Email])) AS [answer]
FROM email
SELECT substring(email, CHARINDEX( ‘@’, [email]) + 1,
LEN([email])) AS [Domain]
From T;
Using ‘mail’ as the column containing the email and ‘table’ as the table/
select substr(email, charindex(‘@’,email)+1, len(email)) as domain_name
from table
Assuming the column of email ids as email_id and table name is customers
SELECT email_id, split_part(email_id, ‘@’, 2) AS Domain
FROM customers;
Suppose we have two columns ‘ID’ and ‘Email’ in a table named ‘mail’. We need to extract the domain name from the mail ids.
The following command can be used for the same.
SELECT ( SUBSTRING_INDEX ( SUBSTR( Email, INSTR ( Email,’@’ ) + 1 ) ) , ‘.’ ,1) as Domain_name from mail ;
INSTR(str1, str2) searches for str2 in str1 and gives the first position at which str1 occurs. ! has been added to t so that a position after the ‘@’ sign starts.
SUBSTR( str, position) gives a substring of str starting from the inputted position.
SUBSTRING_INDEX (str, delimiter, count) gives the substring from string str until the ‘count’ number of delimiter is seen.