AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
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.
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
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;
