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

Description
In this SQL tutorial, we tackle a famous SQL interview question: how to extract the first name, middle name, and last name of a customer from a database. This is a common problem that tests your ability to handle string manipulation and understand the structure of names within a database. We'll walk you through the SQL query to efficiently retrieve these name components, ensuring you're well-prepared for your next technical interview.
Keywords
SQL interview question
SQL tutorial
SQL string manipulation
Extract names in SQL
SQL query example
SQL name extraction
SQL for beginners
SQL for interviews
SQL name components
Tags
#SQL
#SQLTutorial
#InterviewQuestions
#SQLInterview
#StringManipulation
#DatabaseQuery
#SQLExamples
#TechnicalInterview
#LearnSQL
#ProgrammingTutorials
✨ Work-related E- emails can be sent to: [e-mail]

[@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;

[@gajasrikanth4242]

🎉🎉❤🎉🎉

[@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

[@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;

[@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??

[@Tejasri15150]

❤