CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Developer
Developer

Reputation: 33

split FULL_NAME into LAST,FIRST & MIDDLE NAME

How to split names(LAST_NAME,FIRST_NAME & MIDDLE_NAME) from a FULL_NAME column of the following format:

FULL_NAME: BARN,KE ROMAN where FIRST_NAME: KE, MIDDLE_NAME: ROMAN & LAST_NAME: BARN

I was able to write the below sql but, how do I add the MIDDLE_NAME query and also what if the MIDDLE_NAME is null (ex-- FULL_NAME: BARN,KE)

SELECT TRIM (SUBSTR (NAMES, INSTR (NAMES, ',', 1) + 1)) AS FIRST_NAME,
       TRIM (SUBSTR (NAMES, 1, INSTR (NAMES, ',', 1) - 1)) AS LAST_NAME
FROM TABLE

Any help is highly appreciated!

Thanks!

Upvotes: 0

Views: 8083

Answers (2)

Amit Chauhan
Amit Chauhan

Reputation: 11

SELECT Substr(fullname, 1, Instr(fullname, ' ') - 1) AS First_Name, 
       Substr(fullname, Instr(fullname, ' ') + 1)    AS Last_Name 
FROM   emp; 

SELECT Substr('Amit Chauhan', 1, Instr('Amit Chauhan', ' ') - 1) AS First_Name, 
       Substr('Amit Chauhan', Instr('Amit Chauhan', ' ') + 1)    AS Last_Name 
FROM   dual;

Upvotes: 1

JNevill
JNevill

Reputation: 50034

I believe that regexp_substr() is your best bet here:

REGEXP_SUBSTR('BARN,KE ROMAN', '[^ ]*$')

This anchors to the end of the string $ then sort of looks forward until it hits the space. That [^ ] bit is like saying "Every character that isn't a space".

Like others have pointed out, this is a crap shoot if you have a middle name with a space in it. You'll just have to hope to be close if you have names that don't match first,last middle format.

Upvotes: 0

Related Questions