sql - Split the name into Fname, Mname and Lname

Question

Please help with this expression

INPUT

Fullname
SZAFLARSKI,ANDRZEJ S
SZAFLARSKI,ANDRZEJ Santa
SZAFLARSKI rel,ANDRZEJ

OUTPUT

Firstname	Middlename	Lastname
ANDRZEJ	S	SZAFLARSKI	This format for length = 1
ANDRZEJ Santa	null	SZAFLARSKI	This format for length greater than 1
ANDRZEJ	null	SZAFLARSKI rel	It has to search for firstname after comma(,)

If middle name length greater than 1 then firstname is append with middlename and middlename gives null. If middle name length = 1 then split with middle name and First name only up to before space. The search for Firstname should start from comma(,) and the third scenario should match.

Can anybody help with this with an explanation?

edit

select lastN,
       case when length(midN) = 1 then midN end as midN,
       firstN || case when length(midN) > 1 then ' ' || midN end as firstN
  from ( select regexp_substr('BOULAY d,STEPHEN', '([^,]+),', 1, 1, '', 1) as lastN,
                regexp_substr('BOULAY d,STEPHEN', ',([^ ]+)', 1, 1, '', 1) as firstN,
                regexp_substr('BOULAY d,STEPHEN', ' (.+)', 1, 1, '', 1) as midN
           from dual );

In this query I have achieved first 2 scenarios but for 3rd it is failing.

标签： sqloracle

Answer 1

你也可以试试这个：

^  -->  Finds regex that must match at the beginning of the line
$  --> Finds regex that must match at the end of the line.
\w --> A word character, short for [a-zA-Z_0-9]
\s --> A whitespace character, short for [ \t\n\x0b\r\f]

函数 length(regexp_substr(Fullname, ',\w+\s*(\w*)$', 1, 1, '', 1)) 返回中间名路径的长度（如果存在）。

with your_table (Fullname) as (
select 'SZAFLARSKI,ANDRZEJ S'      from dual union all
select 'SZAFLARSKI,ANDRZEJ Santa'  from dual union all
select 'SZAFLARSKI rel,ANDRZEJ'    from dual
)
select Fullname
, case when length(regexp_substr(Fullname, ',\w+\s*(\w*)$', 1, 1, '', 1)) > 1 
        then regexp_substr(Fullname, ',([^,]+)$', 1, 1, '', 1) 
      else regexp_substr(Fullname, ',(\w+)\s*\w*$', 1, 1, '', 1)
  end FirstName
, case when length(regexp_substr(Fullname, ',\w+\s*(\w*)$', 1, 1, '', 1)) > 1 
        then null
      else regexp_substr(Fullname, ',\w+\s*(\w*)$', 1, 1, '', 1)
    end middleName
, regexp_substr(Fullname, '^[^,]+', 1, 1)Lastname 
from your_table
;

db<>小提琴