substr Error -- ORA-01722:invaild number separate string

Question

I am trying to separate first and last name . I have a column called 'Fullname' and it has first and last name and a comma all in one column. I've tried the below but I get an error " its not a valid number". When I remove the comma it works, so I am not sure how to incorporate a comma in the formula so it can work.

,substr(Fullname,1,',') as Lastname ,substr(Fullname,',',' ') as Firstname

Column 

Fullname
Brown,John N
Green,Julie T


Desired results
Lastname    FirstName
Brown        John
Green        Julie

Answer 1

You can use regexp_substr():

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^, ]+', 1, 2) as firstname

Answer 2

The second argument to SUBSTR() is the position of the substring, the third argument is the length of the substring. It will not automatically search for a delimiter if you use strings there instead of numbers. You can use LOCATE() to find the positions that you want.

SUBSTR(Fullname, 1, LOCATE(Fullname, ',')-1) AS Lastname,
SUBSTR(Fullname, LOCATE(Fullname, ',')+1) AS Firstname

Answer 3

Can be performed in Classical way by using instr inside substr function as the following case :

select substr(fullname,1,instr(fullname,',')-1) Firstname,
       substr(fullname,instr(fullname,',')+1,length(fullname)) Lastname
  from tab;

SQL Fiddle Demo