Expert: Suchitra Joshi - 9/30/2009

Question
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,

I did not understand your question properly. If anything is missing in fullname, do you want the query to show those values in appropriate columns and do you don't want it at all ?

eg. Mr Chinku

Should this return Title as Mr and Name as Chinku ?

eg. dipti kumari soni

Should this return Title as dipti, Name and kumari and Middlename as soni?
or Title as null, Name as dipti, Middlename as kumari and Surname as soni ? (this is not possible unless there are spaces in those particular places)

Please let me know so that I can help you further.

Regards

Suchitra

---------- FOLLOW-UP ----------

QUESTION: eg. dipti kumari soni

Should this return Title as null, Name as dipti, Middlename as kumari and Surname as soni ?

yes <space> is there between the words then

u give me the solution how i get it

Answer
Hi Sandeep,

Given the data with possible spaces in Title, middlename and lastname positions, this query is not possible unless we have a fixed length of data for Title, Firstname, MiddleName and Surname.

In case of variable length positions in the fullname column, it is difficult to analyze spaces as seperators or null values.

Here is a sample query which can check for spaces in Title and give appropriate result. But it will not work if firstname or middlename are spaces.


select  a.fullname,
       a.title,
       case instr(a.name,' ',1,1)
         when 0 then substr(a.name, 1)
         else substr(a.name, 1, instr(a.name,' ',1,1)-1)
       end as firstname,
       case instr(a.name,' ',1,2)
         when 0 then case instr(a.name,' ',1,1)
         when 0 then null
         else substr(a.name, instr(a.name,' ',1,1)+1)
         end
         else substr(a.name, instr(a.name,' ',1,1)+1, instr(a.name,' ',1,2)-1-instr(a.name,' ',1,1))
       end as middlename,
       case instr(a.name,' ',1,2)
         when 0 then null
         else substr(a.name, instr(a.name,' ',1,2)+1)
       end as surname
       --substr(a.name, 1, instr(a.name,' ',1,1)-1) firstname,
       --substr(a.name, instr(a.name,' ',1,1)+1, instr(a.name,' ',1,2)-1) middlename,
       --substr(a.name, instr(a.name,' ',1,2)+1) surname
from    (select fullname,
         case length(fullname) - length(trim(fullname))
         -- 0 indicates fullname contains title
         when  0 then substr(fullname,1,instr(fullname,' ',1,1)-1)
         -- null means fullname does not contain title
         else null
         end as title,
         case length(fullname) - length(trim(fullname))
         when  0 then substr(fullname,instr(fullname,' ',1,1)+1)
         else trim(fullname)
         end as name
       from names) a


Hope this clarifies.

Regards

Suchitra