Parsing the last name (from full name field) into temp table

[billy001]

How do I go about grabbing only the last name out of a 'Full Name' field ? I've so far tried executing a parse script in Oracle 9i using SQL Plus and am not successfully getting any data returned. Cannot find any references to the SQL scripts that will do this. Please help. All I need is just the basic SQL command and I can build on that. Thanks.
Bill

[vijaydiwakar]

How do I go about grabbing only the last name out of a 'Full Name' field ? I've so far tried executing a parse script in Oracle 9i using SQL Plus and am not successfully getting any data returned. Cannot find any references to the SQL scripts that will do this. Please help. All I need is just the basic SQL command and I can build on that. Thanks.
Bill

try to use substr delimated by one space and use replace cmd to get the name only

[Dave44]

How do I go about grabbing only the last name out of a 'Full Name' field ? I've so far tried executing a parse script in Oracle 9i using SQL Plus and am not successfully getting any data returned. Cannot find any references to the SQL scripts that will do this. Please help. All I need is just the basic SQL command and I can build on that. Thanks.
Bill

You can use something like this on it if it is delimited by a space:

[160]dave44@ORADB> DECLARE
  2       full_name      VARCHAR2(30) := 'FName MName LName';
  3       first_name     VARCHAR2(30);
  4  BEGIN
  5       first_name      := SUBSTR(full_name,0, INSTR(full_name, ' ', 1) );
  6       DBMS_OUTPUT.put_line(first_name);
  7  END;
  8  /
FName
 
PL/SQL procedure successfully completed.
 

if it is delimited by something other than a space (' ') just change the parameter in the instr call

if it is in a different order in the table then you will have to play with what is here to get the correct field in the string.

Hope this helps.