Expert: Elliot Mak - 9/29/2009

Question
 Fullname                TIT    NAME   MIDDLENAME surname

Mr rahul kumar shukla     Mr    rahul   kumar      shukla
Mr Chinku                 Mr    chinku
Mr rinku prasad           Mr    rinku   prasad
Mr pintu gupta            Mr    pintu              gupta
Mr chintu agrawal         Mr    chintu             agrawal
dipti kumari soni               dipti   kumari     soni
Mr kishor kumar tiwari    Mr    kishor  kumar      tiwari
Mrs asika kumar sinha     mrs   asika   kumar      sinha
mragendra                       mragendra
mah   kum    sura               mah      kum       sura

this is the table i have only one column FULLNAME. All the values containing fullname separated by space like this::

title name middlename fullname.


I want to
the split this fullname and get title, name ,middlename, surname
if any thing missing in the fullname please dont update that value in a column like this

in second row "Mr Chinku" containing only title and name thats why title and name are filled other two column middlename,surname was blanked

please give me the solution like CURSOR ,QUERY any thing

Answer
Hi Sandeep

Here are the rules that I assume according to your question

(1) If fullname contains [TITLE] [NAME] [MIDDLENAME] [SURNAME], for example Mr Rahul Kumar Shukla,
it will map to
  TITLE:  Mr
  NAME:   Rahul
  MIDDLENAME:  Kumar
  SURNAME:  Shukla

(2) If fullname contains [NAME] [MIDDLENAME] [SURNAME], for example Rahul Kumar Shukla,
it will map to
  TITLE:  [NULL]
  NAME:   Rahul
  MIDDLENAME:  Kumar
  SURNAME:  Shukla

(3) If fullname contains [NAME], for example mragendra, it will map to
  TITLE:  [NULL]
  NAME:   mragendra
  MIDDLENAME:  [NULL]
  SURNAME:  [NULL]
  
(4) If full name contains two out of three name elements (title, name, middlename, surname),
for example    Mr Rinku Prasad, it will map to
  TITLE:  Mr
  NAME:   Rinku
  MIDDLENAME:  [NULL]
  SURNAME:  Prasad
  
The rule is to retrieve the title from fullname if it exists.  

If the reminding of the string contains three name elements, then map to NAME, MIDDLENAME, and SURNAME in order

If the reminding of the string contains two name elements, then map to NAME, and SURNAME in order

If the reminding of the string contains one name element, then map to NAME

In the following example, it takes in the consideration that there may be multiple spaces between each name element.
And in addition, you will have to specify a list of TITLE so that it will function properly.  I have provided the sample code to take care of the splitting.  You will have to take care of the insert/update statements yourself.

EXAMPLE:
CREATE TABLE ALLEXPERTS_TBL (FULLNAME VARCHAR2(128));

INSERT INTO ALLEXPERTS_TBL VALUES ('Mr rahul kumar shukla')
INSERT INTO ALLEXPERTS_TBL VALUES ('Mr Chinku');
INSERT INTO ALLEXPERTS_TBL VALUES ('Mr rinku prasad');
INSERT INTO ALLEXPERTS_TBL VALUES ('Mr pintu gupta');
INSERT INTO ALLEXPERTS_TBL VALUES ('Mr chintu agrawal');
INSERT INTO ALLEXPERTS_TBL VALUES ('dipti kumari soni');
INSERT INTO ALLEXPERTS_TBL VALUES ('Mr kishor kumar tiwari');
INSERT INTO ALLEXPERTS_TBL VALUES ('Mrs asika kumar sinha');
INSERT INTO ALLEXPERTS_TBL VALUES ('mragendra');
INSERT INTO ALLEXPERTS_TBL VALUES ('mah   kum    sura');

COMMIT;

SET SERVEROUTPUT ON;
DECLARE

  l_title       VARCHAR2(30):=NULL;
  l_name1      VARCHAR2(128):=NULL;
  l_name2      VARCHAR2(128):=NULL;
  l_name3      VARCHAR2(128):=NULL;
  l_name      VARCHAR2(128):=NULL;
  l_location   NUMBER;
  l_next_location NUMBER;

  CURSOR c_main IS SELECT fullname FROM ALLEXPERTS_TBL;
  
BEGIN
  FOR cursor_data IN c_main LOOP
     -- Get the title
     l_location := INSTR(cursor_data.fullname,' ',1);
     l_title := SUBSTR(cursor_data.fullname,1,l_location-1);
     
     IF UPPER(l_title) NOT IN ('MR','MRS','MS','MR.','MRS.','MS.') THEN
        l_title := NULL;
        l_location := 1;
     END IF;
     
     -- Open Loops to scan the entire string
     -- Using location search because there maybe multiple blank space in between names
     WHILE l_location <= LENGTH(cursor_data.fullname) LOOP
        IF SUBSTR(cursor_data.fullname,l_location,1) <> ' ' THEN
           
           l_next_location := INSTR(cursor_data.fullname,' ', l_location);
           
           IF l_next_location = 0 THEN
              l_next_location := LENGTH(cursor_data.fullname);
           END IF;
           
           l_name := SUBSTR(cursor_data.fullname,l_location,l_next_location-l_location);
           l_location := l_next_location+1;
        
           IF l_name1 IS NULL THEN
              l_name1 := l_name;
           ELSIF l_name2 IS NULL THEN
              l_name2 := l_name;
           ELSIF l_name3 IS NULL THEN
              l_name3 := l_name;
           END IF;
           
        ELSE
           l_location := l_location + 1;            
        END IF;
        
     END LOOP;

  IF l_name1 IS NOT NULL AND l_name2 IS NOT NULL AND l_name3 IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('Full Name: ' || cursor_data.fullname);
     DBMS_OUTPUT.PUT_LINE ('Title: ' || l_title);
     DBMS_OUTPUT.PUT_LINE ('Name: ' || l_name1);
     DBMS_OUTPUT.PUT_LINE ('Middle Name: ' || l_name2);
     DBMS_OUTPUT.PUT_LINE ('Surname: ' || l_name3);
  ELSIF      l_name1 IS NOT NULL AND l_name2 IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('Full Name: ' || cursor_data.fullname);
     DBMS_OUTPUT.PUT_LINE ('Title: ' || l_title);
     DBMS_OUTPUT.PUT_LINE ('Name: ' || l_name1);
     DBMS_OUTPUT.PUT_LINE ('Surname: ' || l_name2);
  ELSIF l_name1 IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('Full Name: ' || cursor_data.fullname);
     DBMS_OUTPUT.PUT_LINE ('Title: ' || l_title);
     DBMS_OUTPUT.PUT_LINE ('Name: ' || l_name1);
  END IF;   
  
        
  -- Reset Variables
  l_location := 0;
  l_next_location := 0;
  l_title := NULL;
  l_name := NULL;
  l_name1 := NULL;
  l_name2 := NULL;
  l_name3 := NULL;
  
  END LOOP;
  

END;
/

Results :
Full Name: Mr rinku prasad
Title: Mr
Name: rinku
Surname: prasa
Full Name: Mr pintu gupta
Title: Mr
Name: pintu
Surname: gupt
Full Name: Mr chintu agrawal
Title: Mr
Name: chintu
Surname: agrawa
Full Name: dipti kumari soni
Title:
Name: dipti
Middle Name: kumari
Surname: son
Full Name: Mr kishor kumar tiwari
Title: Mr
Name: kishor
Middle Name: kumar
Surname: tiwar
Full Name: Mrs asika kumar sinha
Title: Mrs
Name: asika
Middle Name: kumar
Surname: sinh
Full Name: mragendra
Title:
Name: mragendra
Full Name: mah   kum    sura
Title:
Name: mah
Middle Name: kum
Surname: sur

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04   

Hope it helps. If you shall have additional questions, please let me know

Cheers
Elliot