Famous SQL Interview Question | First Name , Middle Name and Last Name of a Customer

[@sahilnaik3904]

with col_transformed as (
select
*,
(len(name) - len(replace(name,' ',''))) as checker,
CHARINDEX(' ',name) as first_space,
CHARINDEX(' ',name,CHARINDEX(' ',name)+1) as second_space
from test_names
)
select
name,
case
when checker = 0
then
name
else
SUBSTRING(name,1,first_space-1)
end as first_name,
case
when checker = 2
then
SUBSTRING(name,first_space+1, (second_space - first_space))
else
null
end as middle_name,
case
when checker != 0
then
case when checker = 1
then SUBSTRING(name,first_space+1, (len(name)-first_space))
else
SUBSTRING(name,second_space+1, (len(name)-second_space))
end
else
null
end as last_name
from col_transformed;

[@chandanpatra1053]

I like your approach to make others understand after writing each query and that too step by step in excel...Great keep it up....looking forward to see 1 video in every two days.....Please bring quality level sql questions.👍👍

[@rajanikanthgaja2916]

Sure definitely thanks for your feedback plz subscribe and share my video's for better reach

[@varunas9784]

Thanks for sharing.
Here's my approach on SQL server:
===========================================
with cte2 as (select *, ROW_NUMBER() over(partition by name order by (select 1)) rn
from names
cross apply string_split(name,' '))
select Max(First_name) First_name, Max(Middle_name) Middle_name, Max(Last_name) Last_name
from (select *,
case when rn = 1 then value end as First_name,
case when COUNT(name) over(partition by name) > 2 and rn = 2 then value end Middle_name,
case when COUNT(name) over(partition by name) < 3 and rn = 2 then value
when COUNT(name) over(partition by name) >= 3 and rn = 3 then value
end Last_name
from cte2) s1
group by name
===========================================

[@rajanikanthgaja2916]

Excellent

[@sabesanj5509]

Hi varun, Thanks for your query. Is your query gave the desired output??

[@sabesanj5509]

WITH cte AS (
SELECT
customer_name,
TRIM(value) AS part,
ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY (SELECT 1)) AS rn
FROM customers
CROSS APPLY STRING_SPLIT(customer_name, ' ')
)
SELECT
customer_name,
MAX(CASE WHEN rn = 1 THEN part END) AS first_name,
MAX(CASE WHEN rn = 2 THEN part END) AS middle_name,
MAX(CASE WHEN rn = 3 THEN part END) AS last_name
FROM cte
GROUP BY customer_name;

[@gajasrikanth4242]

🎉🎉❤🎉🎉

[@Tejasri15150]

❤