How to split source column into multiple target columns ( full name to first and Last)

How to split source column into multiple target columns ( full name to first and Last)

Problem: To split fullname into firstname and lastname to be inserted into Target table.

Approach:

CREATE SET TABLE test

( 

fullname varchar(30)

);

INSERT INTO test12 ('nitin raj');

INSERT INTO test12 ('nitin agarwal');

INSERT INTO test12 ('abhishek gupta');

sel * FROM    test;

fullname

nitin agarwal

nitin raj

abhishek gupta

Use index to find the position of space "SPACE" in full name and then use the position to get

--> firstname  =fullname from 1st till (SPACE-1)

-->lastname = fullname from (SPACE+1)

SELECT      INDEX(fullname ,' ') AS "a", SUBSTR(fullname,1, a-1 ) , SUBSTR(fullname,a+1 )  FROM        test;

a	Substr(fullname,1,(a-1))	Substr(fullname,a)
6	nitin	agarwal
6	nitin	raj
9	abhishek	gupta