we have bunch of tables which has cutomer names in one column, now we need to separate them as firstname, middlename and lastname and then transfer them to new target table. can you please give me the string conversion command/procedure which can do that..many thanks
An ounce of prevention is worth a pound of cure
In which format customer names are stored?
<< FirstName >> << space >> << LastName >>
<< LastName >> << space >> << FirstName >>
The format is importatnt or else u have to relaod the data, manually
Last edited by tabreaz; 09-05-2006 at 03:49 AM.
select substr(trim(empname), 1, instr(trim(empname), ' ', 1, 1)-1) Fname,
substr(trim(empname), instr(trim(empname), ' ', 1, 1)+1,
instr(trim(empname), ' ', -1, 1)- instr(trim(empname), ' ', 1, 1)) Mname,
substr(trim(empname), instr(trim(empname), ' ', -1, 1)+1) Lname
Above Query assumes first name, middle name and last name are separated by space. It also takes care of more than 3 words names.
First and last word will go into firstname and last name. Rest of words will form middle name... ex. colin alis douz kep....
colin --- fname
kep --- lname
alis douz --middle name.
Trim is used to remove spaces from beginning and end.
if names are separated by comma then replace ' ' by ','
Click Here to Expand Forum to Full Width