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 |
Good One, But fails if there is any space at the start to the "fullname" column.
ReplyDeleteCan you help on this?
Hi vickey,
DeleteYou can use trim function with fullname to remove any spaces.
Ltrim for preceding , rtrim for succeeding spaces.
Hope this helps
Vinay.