Thursday, November 19, 2015

Split Fullname into firstname and last name thru sql - Oracle


Today I was working around some queries and I had a requirement where in the data in table was being stored a FULLNAME. Now I have Split Fullname into First Name and Last Name. I have all the fullnames in the column seperated by a empty space ' '.
We can get the desired output by using the combination of SUBSTR and INSTR.


Lets create a sample table :

create table TESTTAB (ID number, FULLNAME varchar2(100));

Insert some data into it:

Insert into TESTTAB (ID,FULLNAME) values (1,'Jaff Schdt');
Insert into TESTTAB (ID,FULLNAME) values (2,'Bradee Will');
Insert into TESTTAB (ID,FULLNAME) values (3,'Kuck Dahl');
Insert into TESTTAB (ID,FULLNAME) values (5,'Melyssa man');
Insert into TESTTAB (ID,FULLNAME) values (6,'Melyssa man');
Insert into TESTTAB (ID,FULLNAME) values (7,'Shart Elarpre');
Insert into TESTTAB (ID,FULLNAME) values (8,'Rock Dihl');
Insert into TESTTAB (ID,FULLNAME) values (9,'Hia Dodd');
Insert into TESTTAB (ID,FULLNAME) values (10,'Pegy Sith');

select * from TESTTAB;

ID FULLNAME
-- -------------
1 Jaff Schdt
2 Bradee Will
3 Rock Dihl
5 Melyssa man
6 Melyssa man
7 Shart Elarpre
8 Rick Dihl
.
.


sample query :

SELECT
  ID,
  FULLNAME,
  SUBSTR(FULLNAME,0,(INSTR(FULLNAME,' ')       -1))  AS FIRST_NAME,
  SUBSTR(FULLNAME,(INSTR(FULLNAME,' ')         +1))  AS LAST_NAME,
  (SUBSTR(FULLNAME,0,(INSTR(FULLNAME,' ')      -1))
  ||SUBSTR(FULLNAME,(INSTR(FULLNAME,' ')))) as CONC_RESLT
FROM
  TESTTAB;

Sample Code Ouput :

ID FULLNAME FNAME LNAME CONCAT_RESLT
--- ------------ ------ ------ --------------

1 Jaff Schdt Jaff Schdt Jaff Schdt
2 Bradee Will Bradee Will Bradee Will
3 Kuck Dahl Kuck Dahl Kuck Dahl
5 Melyssa man Melyssa man Melyssa man
6 Melyssa man Melyssa man Melyssa man
7 Shart Elarpre Shart Elarpre Shart Elarpre
8 Rock Dihl Rock Dihl Rock Dihl
.
.

No comments:

Post a Comment