❤
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;
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.👍👍
Sure definitely thanks for your feedback plz subscribe and share my video's for better reach
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
===========================================
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;
🎉🎉❤🎉🎉