Saturday, September 25, 2010

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



2 comments:

  1. Good One, But fails if there is any space at the start to the "fullname" column.

    Can you help on this?

    ReplyDelete
    Replies
    1. Hi vickey,

      You can use trim function with fullname to remove any spaces.
      Ltrim for preceding , rtrim for succeeding spaces.

      Hope this helps

      Vinay.

      Delete