psftdeveloper psftdeveloper - 5 months ago 23
SQL Question

split FULL_NAME into LAST,FIRST & MIDDLE NAME

How to split names(LAST_NAME,FIRST_NAME & MIDDLE_NAME) from a FULL_NAME column of the following format:

FULL_NAME: BARN,KE ROMAN where FIRST_NAME: KE, MIDDLE_NAME: ROMAN & LAST_NAME: BARN


I was able to write the below sql but, how do I add the MIDDLE_NAME query and also what if the MIDDLE_NAME is null (ex-- FULL_NAME: BARN,KE)

SELECT TRIM (SUBSTR (NAMES, INSTR (NAMES, ',', 1) + 1)) AS FIRST_NAME,
TRIM (SUBSTR (NAMES, 1, INSTR (NAMES, ',', 1) - 1)) AS LAST_NAME
FROM TABLE


Any help is highly appreciated!

Thanks!

Answer

Something like this should work - covering several possible formats for the input string. For example (see the second input) it allows for a space or no space between the comma and the first name.

with input_names (input_str) as (
       select 'Barn,KE  Roman' from dual union all
       select 'Utry, Rita'     from dual union all
       select 'Jam,Ilya-Dimo'  from dual
     )
select regexp_substr(input_str, '^([^,]+),'        , 1, 1, null, 1) as   last_name,
       regexp_substr(input_str, ',\s*([^\s]+)'     , 1, 1, null, 1) as  first_name,
       regexp_substr(input_str, ',\s*[^\s]+\s+(.*)', 1, 1, null, 1) as middle_name
from input_names;

Results:

LAST_NAME            FIRST_NAME           MIDDLE_NAME
-------------------- -------------------- --------------------
Barn                 KE                   Roman
Utry                 Rita
Jam                  Ilya-Dimo