We got a requirement in the redshift table to split the full name column into first_name, middle_name, last_name columns with the help of space in the string using the SQL script. Below is the sample data and expected output for the columns. The first_name and last_name are deriving fine but getting issues for the middle_namewe with the below SQL, it removes the strings in middle name which is the common in other two columns hence it is not working for a few scenarios below are examples

Can you please help us fix this issue?

SQL Query: “Select fullname ,
SUBSTRING(fullname , 1, CHARINDEX(‘ ‘, fullname) – 1) as FirstName,
RTRIM(LTRIM(REPLACE(REPLACE(fullname,SUBSTRING(fullname , 1, CHARINDEX(‘ ‘, fullname) – 1),”),
REVERSE( LEFT( REVERSE(fullname), CHARINDEX(‘ ‘, REVERSE(fullname))-1 ) ),”)))as MiddleName,
REVERSE( LEFT( REVERSE(fullname), CHARINDEX(‘ ‘, REVERSE(fullname))-1 ) ) as LastName
From (select ‘john johnson’ fullname)”