Home > PL/SQL > (PLSQL) How to parse a full name into first, middle and last names?

(PLSQL) How to parse a full name into first, middle and last names?

Thursday, 30 July, 2009 Leave a comment Go to comments

1st Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select ' fabc     iabc     oabcd   ' fio from dual union all
select 'fabc iabc    ' fio from dual union ALL
select '  fabc   ' fio from dual union ALL
SELECT 'fabc  iabc  ' fio from dual
)
SELECT substr(fio, 1, instr(fio, ' ')) AS LastName,
       substr(fio, instr(fio, ' ') + 1,
       instr(substr(fio, instr(fio, ' ') + 1), ' ')) 
       FirstName,
       substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1), ' ') + 1,
       instr(substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1),
       ' ') + 1), ' ')) MiddleName
  FROM
  (
  SELECT
    TRIM(REPLACE(
         REPLACE(REPLACE(fio, ' ', '@!'), '!@'),
         '@!', ' '))
    || ' ' fio FROM t) t;

2nd Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '       fabc iabc     oabcd   ' fio from dual union ALL
select 'fabc     iabc     oabcd   ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select 'fabc iabc ' fio from dual union ALL
SELECT 'fabc  iabc' fio from dual)
SELECT
trim(substr(trim(fio),1,instr(trim(fio),' ',1,1))) 
      AS LastName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,instr
         (TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
         length(trim(fio))))
    ELSE trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1))) END AS FirstName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))), 1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN 'null'
     ELSE trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
          length(trim(fio)))) END AS MiddleName FROM t;

ResultSet

LASTNAME FIRSTNAME MIDDLENAM
——– ——— ———
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
Baimendinov Salimjan Danauly
fabc iabc null
fabc iabc null
Advertisements
Categories: PL/SQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: