https://www.youtube.com/watch?v=tVQUsozKkyI

create table customers  (customer_name varchar(30));

insert into customers values ('Ankit Bansal')
,('Vishal Pratap Singh')
,('Michael'); 

WITH
CASE WHEN
LOCATE()
SUBSTRING()
LEFT()
RIGHT()

WITH cte as (
select 
	*, 
    length(customer_name) - length(replace(customer_name,' ','')) AS len,
    locate(' ',customer_name) AS space_1,
    locate(' ',customer_name,locate(' ',customer_name)+1) AS space_2
from customers
)
SELECT
	*,
    CASE WHEN len = 0 THEN customer_name 
    ELSE substring(customer_name,1,space_1-1) END AS First_name,
    CASE WHEN len<=1 THEN null
    ELSE substring(customer_name,space_1+1,space_2-space_1-1) END AS Middle_name,
    CASE WHEN len = 0 THEN null
    WHEN len = 1 then right(customer_name,space_1) 
    WHEN len = 2 THEN substring(customer_name,space_2+1)
    END AS last_name
FROM cte