Thank you for explaining charindex and stuff functions in sql server. I tried to solve this as below select *, SUBSTRING(email, 2, LEN(email) - 2) as email1 from data_raw
As a fresher i want to join EY for Data analytics role what are the topics should i cover in sql, python. What are the most asked coding questions in Data analytics
You need to cover all of this, as for the role of data analytics there are three primary pillars 1) SQL 2) PYTHON 3) ANY DATA VISUALISATION TOOL (POWER BI..ETC)
i have tried solving it in mysql a bit differently.Please have a look select id,name, mid( mid(email,2,length(email)-1),1,length(mid(email,2,length(email)-1))-1) as email from data_raw
with cte as( select data_raw.*, case when SUBSTRING(Email,1,1)='_' AND SUBSTRING(Email,LENGTH(Email),1)!='"' THEN SUBSTRING(Email,2,LENGTH(Email)-1) when SUBSTRING(Email,LENGTH(Email),1)='"' and SUBSTRING(Email,1,1)='_' THEN SUBSTRING(Email,2,LENGTH(Email)-2) when SUBSTRING(Email,LENGTH(Email),1)='"' and SUBSTRING(Email,1,1)!='_' THEN SUBSTRING(Email,1,LENGTH(Email)-1) ELSE Email END AS final_email FROM data_raw ) select Id,Name,final_email FROM cte;
Superb explanation 👌 👏
Thanks for the feedback, keep watching and keep learning 👍
Thank you for explaining charindex and stuff functions in sql server.
I tried to solve this as below
select *, SUBSTRING(email, 2, LEN(email) - 2) as email1
from data_raw
That's great, but this solution is handling all of the cases?
Nicely explained
Thanks 👍
@@deepankarpathak983 please do more like this stuff
Content is really good and informative
Please keep it up
As a fresher i want to join EY for Data analytics role what are the topics should i cover in sql, python.
What are the most asked coding questions in Data analytics
You need to cover all of this, as for the role of data analytics there are three primary pillars
1) SQL
2) PYTHON
3) ANY DATA VISUALISATION TOOL (POWER BI..ETC)
i have tried solving it in mysql a bit differently.Please have a look
select id,name,
mid(
mid(email,2,length(email)-1),1,length(mid(email,2,length(email)-1))-1) as email
from data_raw
Please check it will handle all of the cases, just like explained in the video.
Thanks a lot for posting the query.
17-0ct-2024
select id,Name,email,replace(replace(email,'_',''),'"','') RectifiedEmails from data_raw;
with cte as(
select data_raw.*,
case
when SUBSTRING(Email,1,1)='_' AND SUBSTRING(Email,LENGTH(Email),1)!='"' THEN SUBSTRING(Email,2,LENGTH(Email)-1)
when SUBSTRING(Email,LENGTH(Email),1)='"' and SUBSTRING(Email,1,1)='_' THEN SUBSTRING(Email,2,LENGTH(Email)-2)
when SUBSTRING(Email,LENGTH(Email),1)='"' and SUBSTRING(Email,1,1)!='_' THEN SUBSTRING(Email,1,LENGTH(Email)-1)
ELSE Email
END AS final_email
FROM data_raw
)
select Id,Name,final_email FROM cte;
select id,name,replace(STUFF(email,1,1,''),'"','')as Email from data_raw;
That's great, but think about the case where we are having " two times and we have to remove only one. How can we solve that.