psftdeveloper psftdeveloper - 3 months ago 9
SQL Question

How to split NAME into 3 different fields FIRST_NAME, MIDDLE_NAME & LAST_NAME when there is a space

How to split NAME into 3 different fields

FIRST_NAME, MIDDLE_NAME & LAST_NAM
E.

Currently the NAME filed is in the following format
[FIRST_NAME || ' ' || MIDDLE_NAME|| ' ' ||LAST_NAME]
. To be more clear suppose a person name is

FIRST_NAME: SHERRY
MIDDLE_NAME: L
LAST_NAME : CLEAVES


then the NAME field has "SHERRY L CLEAVES", I want it in 3 different fields. Also, How do I handle if there are only two names?

some of the sample data as follows:

('William Sears',
'PETER E LABBE',
'Edith Roberts',
'SHERRY L CLEAVES',
'Sharon Matthes',
'TAMMY L PELLETIER',
'STACIE M KINER',
'MICHAEL C THOMAS',
'CHESTER R DAVIS',
'MICHAEL D HUTCHINSON',
'Paul Mikkelsen'
)

Answer

Here's one way to do it:

with data_qry
as
(
select 'William  Sears' as name from dual union all
select 'PETER E LABBE' as name from dual union all
select 'Edith  Roberts' as name from dual union all
select 'SHERRY L CLEAVES' as name from dual union all
select 'Sharon  Matthes' as name from dual union all
select 'TAMMY L PELLETIER' as name from dual union all
select 'STACIE M KINER' as name from dual union all
select 'MICHAEL C THOMAS' as name from dual union all
select 'CHESTER R DAVIS' as name from dual union all
select 'MICHAEL D HUTCHINSON' as name from dual union all
select 'Paul  Mikkelsen' as name from dual
)
select name
     , substr(name, 1, instr(name, ' ', 1)) as first
     , case when instr(name, ' ', 1, 2) > 0 then substr(name, instr(name, ' ', 1, 1) + 1, instr(name, ' ', 1, 2) - instr(name, ' ', 1, 1) - 1) else null end as middle
     , case when instr(name, ' ', 1, 2) > 0 then substr(name, instr(name, ' ', 1, 2) + 1) else substr(name, instr(name, ' ', 1, 1) + 1) end as last
from data_qry

Output:

NAME                 FIRST      MIDDLE     LAST
William  Sears       William               Sears
PETER E LABBE        PETER      E          LABBE
Edith  Roberts       Edith                 Roberts
SHERRY L CLEAVES     SHERRY     L          CLEAVES
Sharon  Matthes      Sharon                Matthes
TAMMY L PELLETIER    TAMMY      L          PELLETIER
STACIE M KINER       STACIE     M          KINER
MICHAEL C THOMAS     MICHAEL    C          THOMAS
CHESTER R DAVIS      CHESTER    R          DAVIS
MICHAEL D HUTCHINSON MICHAEL    D          HUTCHINSON
Paul  Mikkelsen      Paul                  Mikkelsen