Hani,
There are actually a lot of ways to do this. Obviously, the easiest would be to use PL/SQL to determine this information, and then make the code as simple
or complicated as you wanted, based on your data.
Here is another expanded SQL example from the one I sent you before. Again, you’d probably be using PL/SQL instead, but this is a starter for ya
J.
number_of_names: counts the number of spaces, adds one, and that’s the count of different names in the full name string. You could then break it down from
there based on your data, and determine what to do with each.
The other fields are self explanatory. Basically, it comes down to your data and the way it’s entered, but perhaps this will help a little when narrowing
it down.
select
length(‘Larry Mo Curly Joe’)
length(replace(‘Larry Mo Curly Joe’, ’
'))+1
number_of_names,
substr(‘Larry Mo Curly Joe’,1,instr( ‘Larry
Mo Curly Joe’,’ ',1))
first_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ‘,1)+1,(instr( ‘Larry
Mo Curly Joe’,’ ',1,2)
instr(‘Larry Mo Curly Joe’, ’
',1)-1))
second_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ‘,1,2)+1,(instr( ‘Larry
Mo Curly Joe’,’ ',1,3)
instr(‘Larry Mo Curly Joe’, ’
',1,2)-1))
third_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ',-1,1)+1)
last_name
from dual;
Thanks,
Dennis