Oracle/sql query or cursor
Expert: Suchitra Joshi - 9/30/2009
QuestionQUESTION: 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
AnswerHi 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