We have moved to www.dataGenX.net, Keep Learning with us.

Thursday, August 22, 2013

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


Approach:

CREATE SET TABLE test
fullname varchar(30)
);


INSERT INTO test12 ('nitin raj');
INSERT INTO test12 ('nitin agarwal');
INSERT INTO test12 ('abhishek gupta');



select * 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

I hope, You guys like it :-)