Join 100 days of SQL challenges where I have created hand picked SQL interview questions to sky rocket your SQL skills. 100daysofsql.com/
@neerajpathak75856 ай бұрын
This also seems to work fine for me : SELECT split_part(customer_name,' ',1) as first_name ,case when split_part(customer_name,' ',3) ='' then '' else split_part(customer_name,' ',2) end as second_name ,case when split_part(customer_name,' ',3) ='' then split_part(customer_name,' ',2) else split_part(customer_name,' ',3) end as third_name from customers
@Riteshkumar-r5o2 ай бұрын
Simple and easy solution for PostgreSQL thanks buddy for your effort
@neerajpathak75852 ай бұрын
@@Riteshkumar-r5o 👍🏻
@architsrivastava66496 ай бұрын
My interview in 30 mins and was watching your Sql playlist ❤️
@bhumikalalchandani3216 ай бұрын
good luck!!
@ankitbansal66 ай бұрын
All the best 🙂
@CricketLover-qy9nn6 ай бұрын
If u don't mind can you share your interview experience.
@GiriPrasath.D6 ай бұрын
From this question, i learned, Char index, substring, left and right functions, you are SQL hero Ankit. and how to use the substring with len and charindex to extract the first , middle and last name.
@ankitbansal66 ай бұрын
Keep learning 😊
@ayushmi77al6 ай бұрын
In postgresql it's very easy, we can use split_part function.
@ankitbansal66 ай бұрын
Right but thats not available in most of other databases.
@ayushmi77al6 ай бұрын
So true, your SQL interview question series so very helpful, thank you so much for that 🙏
@anudipray44924 ай бұрын
Oracle function much better
@pradeeppatil71685 ай бұрын
Hi Ankit, i guess i have made it more simple, just check it. select *, case when len(empname)-len(replace(empname,' ',''))=1 OR len(empname)-len(replace(empname,' ',''))=2 then substring(empname,1,charindex(' ',empname)) ELSE EmpName end as F_Name, case when len(empname)-len(replace(empname,' ',''))=2 then substring(empname,charindex(' ',empname)+1,CHARINDEX(' ',empname,CHARINDEX(' ',empname)+1)-charindex(' ',empname)) end as M_Name, case when len(empname)-len(replace(empname,' ',''))=1 OR len(empname)-len(replace(empname,' ',''))=2 then substring(empname,charindex(' ',empname,5)+1,len(empname)) end as L_Name from Employee
@dfkgjdflkg6 ай бұрын
Not surprised endlessly impressive mastery that can only be envied. thanks
@usharanikallubhavi74665 ай бұрын
Hi Ankit. To extract middle_name, can we write SUBSTRING(customer_name, first_space_position+1, second_space_position-1)
@yakkaluruvijaysankar7876 ай бұрын
Your mic quality is not good. There is no clarity on what you are explaining. The information is very good and informative. Please do more videos like this.
@jhonsen98426 ай бұрын
Could you please post more Data Engineering SQL Questions . I think these questions are more alligned to Data Analyst which is cool also. Looking some super hard questions on Join and CTE,Subqueries.
@ss-hm6ey2 ай бұрын
Hi Sir, i recently gave an interview at LTIMINDTREE and the very first question they asked was there are three tables and can we use left and right join on them. I didnt understand the question also clearly. Pls make a video on this and explain in detail. Thank you.
@vsagar4b386 ай бұрын
Liked the Logic and Explanation, superb Enjoyed. Thanks Ankit
@ankitbansal66 ай бұрын
Glad you liked it
@DE_Pranav2 ай бұрын
with cte as( select * from customers cross apply string_split(customer_name,' ') ) ,cte2 as( select *, ROW_NUMBER() over(partition by customer_name order by(select null)) as rn, count(*) over (partition by customer_name) as cnt from cte ) select customer_name, max(case when rn=1 then value end) as firstname, max(case when rn=2 and cnt=3 then value end) as middlename, max(case when (rn=2 and cnt=2) or rn=3 then value end) as lastname from cte2 group by customer_name
@Ak12345-g6 ай бұрын
Thanks for sharing good problems❤ worth watching
@ankitbansal66 ай бұрын
Glad you enjoyed it
@rajatbhat20746 ай бұрын
Hi Ankit, Tried below solution in PostgreSQL, its working. Let me know your thoughts. with cte as ( select customer_name, length(customer_name)- length(replace(customer_name,' ','')) as no_of_spaces from customers ) select customer_name, case when no_of_spaces >=0 then split_part(customer_name, ' ', 1) end as first_name, case when no_of_spaces =1 then split_part(customer_name, ' ', no_of_spaces+1) end as last_name from cte;
@ankitbansal66 ай бұрын
It's good but the split part function is not available in most other databases
@addhyasumitra902 күн бұрын
My approach: bit compilcated but inspired by your method and with diff approach: with CTE as ( select customer_name, len(customer_name) - len(REPLACE(customer_name,' ','')) as spaces, value, ROW_NUMBER() OVER(order by customer_name ) as rn from customers cross apply string_split(customer_name, ' ')), CTE2 as ( select customer_name, case when spaces=0 then customer_name when ROW_NUMBER() over(partition by customer_name order by rn)=1 then value end as first_name ,case when spaces
@gauravkakhani71656 ай бұрын
Using right function last name: Case when no_of_spaces= 0 then null when no_of_spaces= 1 then right(customer_name,len(customer_name) - firstspaceposition) else right(customer_name,len(customer_name)- second space position)end as lastname from cte;
@ankitbansal66 ай бұрын
Perfect 💪
@monuoriginal74256 ай бұрын
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....
@roshangangurde71886 ай бұрын
Great explanation sir 🙏🙏
@SiiitiiFreelancing-jl3ty5 ай бұрын
in Postgres? with strpos or position? how is second space found?
@adharshsunny51546 ай бұрын
Please do create AWS videos
@electricalsir6 ай бұрын
Same
@akashsonone28386 ай бұрын
Hello Ankit , I've attempted another approach. Please inform me if it's functioning correctly in every corner case as well. WITH CTC AS( SELECT *, LEN(CUSTOMER_NAME) - LEN(REPLACE(CUSTOMER_NAME,' ','')) AS NO_OF_SPACES, LEFT(CUSTOMER_NAME, CHARINDEX(' ',CUSTOMER_NAME)) AS FIRST_NAME, RIGHT(CUSTOMER_NAME, CHARINDEX(' ',REVERSE(CUSTOMER_NAME))) AS LAST_NAME FROM CUSTOMERS ) SELECT CASE WHEN NO_OF_SPACES = 0 THEN CUSTOMER_NAME ELSE FIRST_NAME END AS FIRST_NAME, CASE WHEN NO_OF_SPACES = 2 THEN SUBSTRING(CUSTOMER_NAME,LEN(FIRST_NAME)+2, LEN(CUSTOMER_NAME)-LEN(FIRST_NAME)-LEN(LAST_NAME)) END AS MIDDLE_NAME, CASE WHEN LEN(LAST_NAME) = 0 THEN NULL ELSE LAST_NAME END AS LAST_NAME FROM CTC
@vikramjitsingh67696 ай бұрын
Those who are looking for MySQL version Solution - select *, substring_index(customer_name, ' ',1) as First , case when x >= 1 then substring_index(customer_name, ' ',-1) else null end as last , case when x >= 2 then substring_index(substring_index(customer_name, ' ',2),' ',-(x-1)) else null end as middle from (select *, length(customer_name) - length(replace(customer_name,' ','')) as x from customers)x
@jececdept.95486 ай бұрын
can use locate as well
@sarathmaya60835 ай бұрын
@ankitbansal6 please review RIGHT function for last name case when no_of_space=0 then null --when no_of_space=1 then SUBSTRING(customer_name,first_space_position+1,first_space_position) --when no_of_space=2 then SUBSTRING(customer_name,second_space_position+1,second_space_position) when no_of_space=1 then RIGHT(customer_name,first_space_position) when no_of_space=2 then RIGHT(customer_name,second_space_position-first_space_position-no_of_space) end as last_name
@naveenvjdandhrudu51416 ай бұрын
Bro just post some easy interview questions in sql
@ankitbansal66 ай бұрын
Ok next time
@NehaAgarwal-l8l5 ай бұрын
with CTE as ( select * from customers cross apply string_split(customer_name,' ') ), CTE2 as (select *,count(*) over(partition by customer_name) as words_count,row_number() over(partition by customer_name order by (select null)) as rn from CTE) select customer_name,max(case when words_count in (1,2,3) and rn=1 then value end) as first_name , max(case when words_count in (3) and rn=2 then value end) as middle_name , max(case when words_count in (2) and rn=2 or words_count in (3) and rn=3 then value end) as last_name from CTE2 group by customer_name
@myselfrithish6 ай бұрын
What is meant by SQL and t SQL is it necessary for data analytics job
@ishika7585Ай бұрын
with cte as ( select customer_name, value AS part, ROW_NUMBER() over (partition by customer_name order by customer_name) AS part_number from customers cross apply STRING_SPLIT(customer_name, ' ') ) ,final as ( select customer_name, max(case when part_number = 1 then part end) as first_name, max(case when part_number = 2 then part end) as middle_name, max(case when part_number = 3 then part end) as last_name from cte group by customer_name ) select * from final;
@anirbanbiswas7624Ай бұрын
SOLUTION FOR MYSQL BY USING SPACE_COUNT ONLY with cte as(select *, length(customer_name)-length(replace(customer_name,' ','')) as space_count from customers1) select customer_name, case when space_count=0 or space_count=1 or space_count=2 then substring_index(customer_name,' ',1) end as trial, case when space_count=2 then substring_index(substring_index(customer_name,' ',2),' ',-1) else null end as mid_name, case when space_count=2 or space_count=1 then substring_index(customer_name,' ',-1) else null end as last_name from cte
@priyanshushak77054 ай бұрын
*for mysql versions which does not have charindex and its equivalent or if there are multiple middle names* : use below approach with cte_spaces as ( select * , length(customer_name)- length(replace( customer_name, ' ','')) as no_of_spaces from customers ) select * , substring_index(customer_name, " ",1) as first_name, case when no_of_spaces > 1 then substring_index(substring_index(customer_name, " ", (-1 * no_of_spaces)), " ", no_of_spaces -1) end as middle_name, case when no_of_spaces > 0 then substring_index(customer_name, " ",-1) end as last_name from cte_spaces
@apurvasaraf58286 ай бұрын
with cte as (select *,LEN(customer_name)-len(REPLACE(customer_name,' ','')) as l ,CHARINDEX(' ',customer_name) as f, CHARINDEX(' ',customer_name,CHARINDEX(' ',customer_name)+1) as s from Customers) select *,case when l=0 then customer_name else substring(customer_name,1,f-1) end as firstn, case when l
@naveenvjdandhrudu51416 ай бұрын
There is only medium & complex in your play list
@kedarwalavalkar68616 ай бұрын
My solution : with cte as ( select *, substring_index(customer_name,' ',1) as a ,substring_index(substring_index(customer_name,' ',2),' ',-1) as b ,substring_index(customer_name,' ',-1) as c ,round((length(customer_name) - length(replace(customer_name,' ','')))/length(' '),0) as leng from custs ) select a as first_name ,case when leng = 2 then b else null end as middle_name ,case when leng = 1 then b when leng = 2 then c else null end as last_name from cte;
@DIVYATRIPATHI-w9f6 ай бұрын
Hello Sir in my sql it is showing error. I have used INSTR Function
@ankitbansal66 ай бұрын
Cool
@AmanRaj-p8w5 ай бұрын
MySQL Solution: with cte as ( select customer_name, (length(customer_name) - length(replace(customer_name, ' ', '')) + 1) as cnt_of_words from customers ) SELECT CASE WHEN cnt_of_words = 1 THEN customer_name -- Only one word, consider it as the first name WHEN cnt_of_words = 2 THEN SUBSTRING_INDEX(customer_name, ' ', 1) -- Two words, consider the first word as first name WHEN cnt_of_words = 3 THEN SUBSTRING_INDEX(customer_name, ' ', 1) -- Three words, consider the first word as first name END AS first_name, CASE WHEN cnt_of_words = 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(customer_name, ' ', 2), ' ', -1) -- Three words, consider the second word as middle name END AS middle_name, CASE WHEN cnt_of_words >= 2 THEN SUBSTRING_INDEX(customer_name, ' ', -1) -- At least two words, consider the last word as last name END AS last_name FROM cte;
@Hope-xb5jv6 ай бұрын
Logic fail if two or three space between name
@ankitbansal66 ай бұрын
You can trim to single space first
@Tech_world-bq3mw6 ай бұрын
You logic will fail if there is space in starting or in ending of string.
@ankitbansal66 ай бұрын
In that case you can just trim the customer name in first cte and then as it is it will work
@samiphani24734 ай бұрын
SELECT customer_name, CASE WHEN size(split(customer_name, ' ')) = 2 THEN split(customer_name, ' ')[0] ELSE split(customer_name, ' ')[0] END AS First_Name, CASE WHEN size(split(customer_name, ' ')) = 2 THEN NULL ELSE split(customer_name, ' ')[1] END AS Middle_Name, CASE WHEN size(split(customer_name, ' ')) = 2 THEN split(customer_name, ' ')[1] ELSE split(customer_name, ' ')[2] END AS Last_Name FROM customers;
@snehalpattewar78645 ай бұрын
SELECT SUBSTRING_INDEX(name, ' ', 1) AS first_name, CASE WHEN LENGTH(name) - LENGTH(REPLACE(name, ' ', '')) > 1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', -2), ' ', 1) ELSE NULL END AS middle_name, SUBSTRING_INDEX(name, ' ', -1) AS last_name FROM your_table_name;
@rubyshorts281Ай бұрын
for mysql users WITH cte AS ( SELECT *, LENGTH(customer_name) - LENGTH(REPLACE(customer_name, ' ', '')) AS spaces FROM customers ) SELECT CASE WHEN spaces = 2 THEN SUBSTRING_INDEX( SUBSTRING_INDEX(customer_name, ' ', -spaces), ' ', spaces - 1 ) ELSE NULL END AS middle, CASE WHEN spaces >= 1 THEN SUBSTRING_INDEX(customer_name, ' ', -1) -- Last name ELSE NULL END AS last FROM cte;
@iramansari36256 ай бұрын
what if we have more then 2 space then @ankitbansal6 ?
PySpark Version of this problem : kzbin.info/www/bejne/kKOZhoucqdOkhbc
@chrishkumar12503 ай бұрын
MYSQL SELECT substring_index(customer_name," ",1) as first_name, if (length(customer_name) - length(replace(customer_name, ' ', '')) > 1 , substring_index(substring_index(customer_name," ",2), " ",-1), Null) as second_name, if (length(customer_name) - length(replace(customer_name, ' ', '')) >= 2 , substring_index(substring_index(customer_name," ",3), " ",-1), Null) as third_name from customers
@sahilummat85553 ай бұрын
;with cte as ( select *, LEN(customer_name)- len(REPLACE(customer_name,' ','')) as spaces, CHARINDEX(' ',customer_name) as space_position, CHARINDEX(' ',customer_name,CHARINDEX(' ',customer_name)+1) as space_position_2 from customers) select *, case when spaces=0 then customer_name when spaces!=0 then left(customer_name,space_position-1) end as first_name , case when spaces>1 then SUBSTRING(customer_name,space_position+1,space_position_2-space_position) end as middle_name , case when spaces=1 then right(customer_name,len(customer_name)-space_position) when spaces>1 then right(customer_name,len(customer_name)-space_position_2) end as last_name from cte
@anudipray4492Ай бұрын
Anyone from oracle can do it
@paritoshjoshi56236 ай бұрын
with temp as( select customer_name, length(customer_name)-length(replace(customer_name,' ','')) ct from customers) select substring_index(customer_name,' ',1) as first_name ,if(ct=2,substring_index(substring_index(customer_name,' ',-2),' ',1) ,null) as middle_name ,if(ct=1 or ct=2,substring_index(customer_name,' ',-1),null) as last_name from temp;
@akashmukherjee90114 ай бұрын
with cte as ( select customer_name, length(customer_name)-length(replace(customer_name,' ','')) as no_of_spaces from customers) select *, case when no_of_spaces = 0 then customer_name when no_of_spaces = 1 then substring_index(customer_name,' ',1) when no_of_spaces = 2 then substring_index(customer_name,' ',1) else null end as first_name, case when no_of_spaces = 2 then substring_index(substring_index(customer_name,' ',-2),' ',1) else null end as middle_name, case when no_of_spaces = 1 then substring_index(customer_name,' ',-1) when no_of_spaces = 2 then substring_index(customer_name,' ',-1) else null end as last_name from cte
@TanmayModi064 ай бұрын
This Works fine: with cte as( select *, ROW_NUMBER() over(order by customer_name) as rn from customers ), cte1 as( select value, rn, ROW_NUMBER() over(partition by rn order by rn) as rk from cte cross apply string_split(customer_name, ' ') ), cte2 as( select *, case when rk = 1 then value end as firstname, case when rk 1 and rk (select Max(rk) from cte1 where rn = a.rn) then value end as middle_name, case when rk = (select Max(rk) from cte1 where rn = a.rn) and rk 1 then value end as lastname from cte1 a ) select STRING_AGG(firstname,'') as firstname, STRING_AGG(middle_name,'') as middle_name, STRING_AGG(lastname,'') as lastname from cte2 group by rn
@rahulkanojiya62564 ай бұрын
another way to solve the same question without using string fuctions : with cte as( select customer_name, value ,row_number() over (partition by customer_name order by customer_name) as rnk ,count(value) over(partition by customer_name order by customer_name) as cnt from customers cross apply string_split(customer_name , ' ') ) ,cte2 as ( select customer_name ,case when rnk = 1 then value end as first_name ,case when rnk = 2 and cnt = 3 then value end as middle_name ,case when (rnk = 2 and cnt = 2) or (rnk = 3 or cnt = 3) then value end as last_name from cte ) select customer_name , max(first_name) as first_name , max(middle_name) as middle_name , max(last_name) as last_name from cte2 group by customer_name
@Riteshkumar-r5o2 ай бұрын
Hi Ankit, Good day! Can you please provide the solution for this question in PostgreSQL, as Position function in Postgre takes only two arguments, that's creating trouble getting the position of second space in the customer_name field. If not possible can you please just suggest me which function to use here for getting second space position in postgresql. Anyone from the community can suggest please.
@mangeshbhumkar20756 ай бұрын
In bigquery possible with split function with safe_offset(0),safe_offset(1) and safe_offset(2)
@SwathiRavichandran-xh8wq3 ай бұрын
Hi Ankit ..your videos are good . Can you help how this can be achieved in oracle sql
@boogieman88276 ай бұрын
how much SQL and python is same in Data Engineering vs Data Analytics?
@007SAMRATROY6 ай бұрын
So if there are N number of words in the name, we will have to derive N - 1 number of space positions right?
@vinil92123 ай бұрын
can middle name be extracted with LEFT or RIGHT?
@hsk77156 ай бұрын
it's look difficult
@prakritigupta34774 ай бұрын
This is the solution in PostgresSQL select split_part(customer_name,' ',1) as first_name, split_part(customer_name,' ',2) as middle_name, split_part(customer_name,' ',3) as last_name from customers;
@ankitbansal64 ай бұрын
If there is no middle name then your query will give last name as null and middle name will be last name.
@shyamu4316 ай бұрын
Thanks Ankit, your questions and the way you solve it, is amazing. It's very good way to build the logical understanding by wathcing your videos. Coming to this particular video. If the name has more than 3 words i.e. Vijay Pratap Singh Rathore. The query will become more complex. Is there any other method to solve it. Once again, I appreciate your efforts in making these wonderful tutorials.
@ankitbansal66 ай бұрын
Yes it will become complex. Also you need to decide what you want to keep in the middle name and last name. In postgres and redshift we have a split part function which can make the solution easy . I hope other databases introduce that function.
@Ashu232004 ай бұрын
Superb!
@srinubathina71916 ай бұрын
Thank you Bro again explanation is next level
@electricalsir6 ай бұрын
Love you 💓
@zainaltaf49356 ай бұрын
Just confused from where u find such questions 😅
@saipranay13186 ай бұрын
I feel the same !! lol 😀
@deepeshmatkati30586 ай бұрын
Great explanation Ankit
@boogieman88276 ай бұрын
Is Syllabus for Data engineer and Data Analyst the same? How much are the similarities?
@Tech_world-bq3mw6 ай бұрын
its different
@boogieman88276 ай бұрын
@@Tech_world-bq3mw how much SQL and python is same in Data Engineering vs Data Analytics?
@arvindgurjar33006 ай бұрын
Thank you so much
@ankitbansal66 ай бұрын
You're most welcome
@nipunshetty96405 ай бұрын
Hii Ankit bansal from my Side small request, As told by you in UR LINKEDIN PROFILE, Supply chain Analytics, Could you make one Video of SUPPLY CHAIN ANALYTICS BY TAKING PROCUREMENT SUPPLY CHAIN DATA SET AND Do DATA ANALYSIS so it will help me and even every audience please My Request Sir @ankitbansal6