customer_name

[farrokhp]

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

[tabreaz]

In which format customer names are stored?

<< FirstName >> << space >> << LastName >>
or
<< LastName >> << space >> << FirstName >>

The format is importatnt or else u have to relaod the data, manually

[rakesh.singh]

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
From tname


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