How to divide one column into several columns

Question

I have one column which I want to divide into three columns

Name
Jack,Jon Man

to be like

first_name  , middle_name , last_name
jack        , Jon         , Man

The first name is followed by a comma and the middle name is followed by space.

Answer 1

Sample data :

name
---------
Jack,Jon Man
Dave,Mike Doe
Chris,Brad Duke

Query :

select substr(name, 0, instr(name, ',')-1) as first_name,
    substr(name, instr(name,',',1,1)+1, instr(name, ',')-1) as middle_name,
    substr(name, instr(name,' ',1,1), instr(name, ' ')+10) as last_name
from sample_data

Output :

first_name    middle_name    last_name
---------------------------------------
Jack          Jon            Man
Dave          Mike           Doe
Chris         Brad           Duke

Goodluck and Merry Christmas!

Answer 2

This is easy to do with the REGEXP_SUBSTR() function. Find out more.

This query identifies the first, second and third occurrence of alphabetical characters. Provided there is a delimiter it doesn't matter what it is.

SQL> select col1 
  2         , regexp_substr(col1, '[[:alpha:]]+') first_name 
  3         , regexp_substr(col1, '[[:alpha:]]+', 1, 2) middle_name 
  4         , regexp_substr(col1, '[[:alpha:]]+', 1, 3) last_name 
  5  from t34;

COL1                           FIRST_NAME MIDDLE_NAM LAST_NAME
------------------------------ ---------- ---------- ----------
Jack,Jon Man                   Jack       Jon        Man

SQL> 

Answer 3

If we are just selecting the data, it can be done using simple substring functions. For instance

 create table #Random
(
Name Varchar(50)
)
insert into #random values ('Jack,Jon Man')

select substring(Name,1,4)'First_Name', 
  substring(Name,6,3)'Middle_Name',
  substring(Name,10,3)'Last_Name'
from #Random