SQL: проанализировать имя, отчество и фамилию из поля полного имени

Если у вас нет очень, очень хорошо обработанных данных, это нетривиальная задача. Наивный подход состоял бы в том, чтобы разметить пробелы и предположить, что результат с тремя токенами будет [первый, средний, последний], а результат с двумя токенами - [первый, последний], но вам придется иметь дело с несколькими токенами. словесные фамилии (например, «Ван Бурен») и несколько отчества.

Вы уверены, что полное официальное имя всегда будет включать имя, отчество и фамилию? Я знаю людей, у которых есть только одно полное имя, и, честно говоря, я не уверен, это их имя или фамилия. :-) Я также знаю людей, у которых есть более одного имени Fisrt в их официальном имени, но у которых нет отчества. И есть люди, у которых несколько отчество.

Также есть порядок имен в Полном юридическом имени. Насколько мне известно, в некоторых азиатских культурах фамилия стоит первой в полном юридическом имени.

С практической точки зрения, вы можете разделить полное имя на пробелы и поставить под угрозу первый токен как имя и последний токен (или единственный токен в случае только одного имени) как фамилию. Хотя это предполагает, что порядок всегда будет одинаковым.

1. Получите функцию регулярного выражения sql. Пример: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
2. Извлекайте имена с помощью регулярных выражений.

Я рекомендую Expresso для изучения / построения / тестирования регулярных выражений. Старая бесплатная версия, новая коммерческая версия

Сложно ответить, не зная, как форматируется «полное имя».

Это может быть «Фамилия, Имя, отчество» или «Имя, отчество, фамилия» и т. д.

В основном вам придется использовать функцию ПОДСТАВКА

SUBSTRING ( expression , start , length )

И, наверное, функция CHARINDEX

CHARINDEX (substr, expression)

Чтобы определить начало и длину каждой части, которую вы хотите извлечь.

Итак, допустим, формат "Имя Фамилия" вы можете (не проверено ... но должно быть близко):

SELECT 
SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) AS FirstName, 
SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, len(fullname)) AS LastName
FROM YourTable

Поменяйте проблему наоборот, добавьте столбцы, чтобы удерживать отдельные части, и объедините их, чтобы получить полное имя.

Причина, по которой это будет ответ Лучший, заключается в том, что нет гарантированного способа выяснить, какое имя зарегистрировано человеком и какое у него отчество.

Например, как бы вы это разделили?

Jan Olav Olsen Heggelien

Это, хотя и вымышленное, является законным названием в Норвегии, и его можно, но не обязательно, разделить следующим образом:

First name: Jan Olav
Middle name: Olsen
Last name: Heggelien

или вот так:

First name: Jan Olav
Last name: Olsen Heggelien

или вот так:

First name: Jan
Middle name: Olav
Last name: Olsen Heggelien

Я полагаю, что подобные случаи можно найти на большинстве языков.

Поэтому вместо того, чтобы пытаться интерпретировать данные, для которых недостаточно информации, сохраните правильную интерпретацию и объедините их, чтобы получить полное имя.

Я не уверен насчет SQL-сервера, но в postgres вы можете сделать что-то вроде этого:

SELECT 
  SUBSTRING(fullname, '(\w+)') as firstname,
  SUBSTRING(fullname, '\w+\s(\w+)\s\w+') as middle,
  COALESCE(SUBSTRING(fullname, '\w+\s\w+\s(\w+)'), SUBSTRING(fullname, '\w+\s(\w+)')) as lastname
FROM 
public.person

Выражения регулярных выражений, вероятно, могли бы быть немного более краткими; но вы поняли суть. Это, кстати, не работает для людей, имеющих два двойных имени (в Нидерландах это много «Ян ван дер Плоег»), поэтому я был бы очень осторожен с результатами.

Как сказал №1, это нетривиально. Перенесенные через дефис фамилии, инициалы, двойные имена, обратная последовательность имен и множество других аномалий могут испортить вашу тщательно продуманную функцию.

Вы можете использовать стороннюю библиотеку (плагин / отказ от ответственности - я работал над этим продуктом):

http://www.melissadata.com/nameobject/nameobject.htm

Я бы сделал это как итеративный процесс.

1) Выгрузите таблицу в плоский файл для работы.

2) Напишите простую программу, чтобы разбить ваши имена, используя пробел в качестве разделителя, где первый токен - это имя, если есть 3 токена, то токен 2 - это отчество, а токен 3 - это фамилия. Если есть 2 жетона, то второй жетон - это фамилия. (Perl, Java или C / C++, язык не имеет значения)

3) Оцените результаты. Ищите имена, которые не подходят под это правило.

4) Используя этот пример, создайте новое правило для обработки этого исключения ...

5) Промыть и повторить

В конце концов вы получите программу, которая исправит все ваши данные.

Вот автономный пример с легко управляемыми тестовыми данными.

В этом примере, если у вас есть имя, состоящее из более чем трех частей, тогда все "лишние" вещи будут помещены в поле LAST_NAME. Исключение делается для определенных строк, которые обозначены как «заголовки», например «DR», «MRS» и «MR».

Если отчество отсутствует, вы получите только FIRST_NAME и LAST_NAME (MIDDLE_NAME будет NULL).

Вы можете разбить его на гигантский вложенный объект SUBSTRING, но читаемость достаточно сложна, как когда вы делаете это в SQL.

Изменить - обрабатывать следующие особые случаи:

1 - Поле NULL NULL

2 - Поле NAME содержит начальные / конечные пробелы

3 - Поле NAME содержит> 1 последовательный пробел в имени.

4 - Поле ИМЯ содержит ТОЛЬКО имя

5 - Включите исходное полное имя в окончательный вывод в виде отдельного столбца для удобства чтения.

6 - Обрабатывать определенный список префиксов как отдельный столбец «заголовок».

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA
 ,FIRST_NAME.TITLE
 ,FIRST_NAME.FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
       THEN NULL  --no more spaces?  assume rest is the last name
       ELSE SUBSTRING(
                       FIRST_NAME.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
                     )
       END AS MIDDLE_NAME
 ,SUBSTRING(
             FIRST_NAME.REST_OF_NAME
            ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
            ,LEN(FIRST_NAME.REST_OF_NAME)
           ) AS LAST_NAME
FROM
  (  
  SELECT
    TITLE.TITLE
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN TITLE.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,1
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
                       )
    END AS FIRST_NAME
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)  
         THEN NULL  --no spaces @ all?  then 1st name is all we have
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
                        ,LEN(TITLE.REST_OF_NAME)
                       )
    END AS REST_OF_NAME
   ,TITLE.ORIGINAL_INPUT_DATA
  FROM
    (   
    SELECT
      --if the first three characters are in this list,
      --then pull it as a "title".  otherwise return NULL for title.
      CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
           ELSE NULL
           END AS TITLE
      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
           ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
           END AS REST_OF_NAME
     ,TEST_DATA.ORIGINAL_INPUT_DATA
    FROM
      (
      SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
       ,FULL_NAME AS ORIGINAL_INPUT_DATA
      FROM
        (
        --if you use this, then replace the following
        --block with your actual table
              SELECT 'GEORGE W BUSH' AS FULL_NAME
        UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
        UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
        UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
        UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
        UNION SELECT 'TOMMY' AS FULL_NAME
        UNION SELECT 'BILLY' AS FULL_NAME
        UNION SELECT NULL AS FULL_NAME
        UNION SELECT ' ' AS FULL_NAME
        UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
        UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
        UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
        UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME      
        ) RAW_DATA
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME

Однажды я сделал регулярное выражение из 500 символов для синтаксического анализа имени, фамилии и отчества из произвольной строки. Даже с этим сигнализирующим регулярным выражением точность составила около 97% из-за полной несогласованности ввода. Тем не менее, лучше, чем ничего.

С учетом уже высказанных предостережений относительно пробелов в именах и других аномалий следующий код будет обрабатывать как минимум 98% имен. (Примечание: беспорядочный SQL, потому что у меня нет опции регулярного выражения в базе данных, которую я использую.)

** Предупреждение: беспорядочный SQL следует:

create table parsname (fullname char(50), name1 char(30), name2 char(30), name3 char(30), name4 char(40));
insert into parsname (fullname) select fullname from ImportTable;
update parsname set name1 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
update parsname set name2 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
update parsname set name3 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
update parsname set name4 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
// fullname now contains the last word in the string.
select fullname as FirstName, '' as MiddleName, '' as LastName from parsname where fullname is not null and name1 is null and name2 is null
union all
select name1 as FirstName, name2 as MiddleName, fullname as LastName from parsname where name1 is not null and name3 is null

Код работает, создавая временную таблицу (parsname) и размечая полное имя пробелами. Любые имена, заканчивающиеся значениями в name3 или name4, не соответствуют требованиям, и с ними нужно будет обращаться по-другому.

Вот хранимая процедура, которая помещает первое найденное слово в Имя, последнее слово в Фамилию и все, что между ними, в Среднее имя.

create procedure [dbo].[import_ParseName]
(            
    @FullName nvarchar(max),
    @FirstName nvarchar(255) output,
    @MiddleName nvarchar(255) output,
    @LastName nvarchar(255)  output
)
as
begin

set @FirstName = ''
set @MiddleName = ''
set @LastName = ''  
set @FullName = ltrim(rtrim(@FullName))

declare @ReverseFullName nvarchar(max)
set @ReverseFullName = reverse(@FullName)

declare @lengthOfFullName int
declare @endOfFirstName int
declare @beginningOfLastName int

set @lengthOfFullName = len(@FullName)
set @endOfFirstName = charindex(' ', @FullName)
set @beginningOfLastName = @lengthOfFullName - charindex(' ', @ReverseFullName) + 1

set @FirstName = case when @endOfFirstName <> 0 
                      then substring(@FullName, 1, @endOfFirstName - 1) 
                      else ''
                 end

set @MiddleName = case when (@endOfFirstName <> 0 and @beginningOfLastName <> 0 and @beginningOfLastName > @endOfFirstName)
                       then ltrim(rtrim(substring(@FullName, @endOfFirstName , @beginningOfLastName - @endOfFirstName))) 
                       else ''
                  end

set @LastName = case when @beginningOfLastName <> 0 
                     then substring(@FullName, @beginningOfLastName + 1 , @lengthOfFullName - @beginningOfLastName)
                     else ''
                end

return

end 

И вот я это звоню.

DECLARE @FirstName nvarchar(255),
        @MiddleName nvarchar(255),
        @LastName nvarchar(255)

EXEC    [dbo].[import_ParseName]
        @FullName = N'Scott The Other Scott Kowalczyk',
        @FirstName = @FirstName OUTPUT,
        @MiddleName = @MiddleName OUTPUT,
        @LastName = @LastName OUTPUT

print   @FirstName 
print   @MiddleName
print   @LastName 

output:

Scott
The Other Scott
Kowalczyk

Как говорят все остальные, вы не можете сделать это простым программным способом.

Рассмотрим эти примеры:

• Президент "Джордж Герберт Уокер Буш" (первый средний средний последний)

• Президентский убийца "Джон Уилкс Бут" (Первый средний Последний)

• Гитарист "Эдди Ван Хален" (Первый Последний Последний)

• И его мама, вероятно, называет его Эдвард Лодевийк Ван Хален (Первый Средний Последний Последний)

• Знаменитая потерпевшая крушение "Мэри Энн Саммерс" (первый, первый, последний)

• Председатель Республиканской партии Нью-Мексико "Фернандо Си де Бака" (Первый Последний Последний Последний)

Мы, конечно, все понимаем, что не существует идеального способа решить эту проблему, но некоторые решения могут помочь вам продвинуться дальше, чем другие.

В частности, довольно легко выйти за рамки простых разделителей пробелов, если у вас есть только несколько списков общих префиксов (Mr, Dr, Mrs и т. д.), Инфиксов (von, de, del и т. д.), Суффиксов (Jr, III , Sr и т. д.) И т. Д. Также полезно, если у вас есть несколько списков общих имен (на разных языках / культурах, если ваши имена разные), чтобы вы могли угадать, может ли слово в середине быть частью фамилии или нет.

BibTeX также реализует некоторые эвристики, которые помогут вам в этом; они инкапсулированы в модуль perl Text::BibTeX::Name. Вот небольшой пример кода, который выполняет разумную работу.

use Text::BibTeX;
use Text::BibTeX::Name;
$name = "Dr. Mario Luis de Luigi Jr.";
$name =~ s/^\s*([dm]rs?.?|miss)\s+//i;
$dr=;
$n=Text::BibTeX::Name->new($name);
print join("\t", $dr, map "@{[ $n->part($_) ]}", qw(first von last jr)), "\n";

Если вы пытаетесь разобрать человеческое имя в PHP, я рекомендую Скрипт nameparse.php Кейта Бекмана.

Копировать на случай выхода сайта из строя:

<?
/*
Name:   nameparse.php
Version: 0.2a
Date:   030507
First:  030407
License:    GNU General Public License v2
Bugs:   If one of the words in the middle name is Ben (or St., for that matter),
        or any other possible last-name prefix, the name MUST be entered in
        last-name-first format. If the last-name parsing routines get ahold
        of any prefix, they tie up the rest of the name up to the suffix. i.e.:

        William Ben Carey   would yield 'Ben Carey' as the last name, while,
        Carey, William Ben  would yield 'Carey' as last and 'Ben' as middle.

        This is a problem inherent in the prefix-parsing routines algorithm,
        and probably will not be fixed. It's not my fault that there's some
        odd overlap between various languages. Just don't name your kids
        'Something Ben Something', and you should be alright.

*/

function    norm_str($string) {
    return  trim(strtolower(
        str_replace('.','',$string)));
    }

function    in_array_norm($needle,$haystack) {
    return  in_array(norm_str($needle),$haystack);
    }

function    parse_name($fullname) {
    $titles         =   array('dr','miss','mr','mrs','ms','judge');
    $prefices       =   array('ben','bin','da','dal','de','del','der','de','e',
                            'la','le','san','st','ste','van','vel','von');
    $suffices       =   array('esq','esquire','jr','sr','2','ii','iii','iv');

    $pieces         =   explode(',',preg_replace('/\s+/',' ',trim($fullname)));
    $n_pieces       =   count($pieces);

    switch($n_pieces) {
        case    1:  // array(title first middles last suffix)
            $subp   =   explode(' ',trim($pieces[0]));
            $n_subp =   count($subp);
            for($i = 0; $i < $n_subp; $i++) {
                $curr               =   trim($subp[$i]);
                $next               =   trim($subp[$i+1]);

                if ($i == 0 && in_array_norm($curr,$titles)) {
                    $out['title']   =   $curr;
                    continue;
                    }

                if (!$out['first']) {
                    $out['first']   =   $curr;
                    continue;
                    }

                if ($i == $n_subp-2 && $next && in_array_norm($next,$suffices)) {
                    if ($out['last']) {
                        $out['last']    .=  " $curr";
                        }
                    else {
                        $out['last']    =   $curr;
                        }
                    $out['suffix']      =   $next;
                    break;
                    }

                if ($i == $n_subp-1) {
                    if ($out['last']) {
                        $out['last']    .=  " $curr";
                        }
                    else {
                        $out['last']    =   $curr;
                        }
                    continue;
                    }

                if (in_array_norm($curr,$prefices)) {
                    if ($out['last']) {
                        $out['last']    .=  " $curr";
                        }
                    else {
                        $out['last']    =   $curr;
                        }
                    continue;
                    }

                if ($next == 'y' || $next == 'Y') {
                    if ($out['last']) {
                        $out['last']    .=  " $curr";
                        }
                    else {
                        $out['last']    =   $curr;
                        }
                    continue;
                    }

                if ($out['last']) {
                    $out['last']    .=  " $curr";
                    continue;
                    }

                if ($out['middle']) {
                    $out['middle']      .=  " $curr";
                    }
                else {
                    $out['middle']      =   $curr;
                    }
                }
            break;
        case    2:
                switch(in_array_norm($pieces[1],$suffices)) {
                    case    TRUE: // array(title first middles last,suffix)
                        $subp   =   explode(' ',trim($pieces[0]));
                        $n_subp =   count($subp);
                        for($i = 0; $i < $n_subp; $i++) {
                            $curr               =   trim($subp[$i]);
                            $next               =   trim($subp[$i+1]);

                            if ($i == 0 && in_array_norm($curr,$titles)) {
                                $out['title']   =   $curr;
                                continue;
                                }

                            if (!$out['first']) {
                                $out['first']   =   $curr;
                                continue;
                                }

                            if ($i == $n_subp-1) {
                                if ($out['last']) {
                                    $out['last']    .=  " $curr";
                                    }
                                else {
                                    $out['last']    =   $curr;
                                    }
                                continue;
                                }

                            if (in_array_norm($curr,$prefices)) {
                                if ($out['last']) {
                                    $out['last']    .=  " $curr";
                                    }
                                else {
                                    $out['last']    =   $curr;
                                    }
                                continue;
                                }

                            if ($next == 'y' || $next == 'Y') {
                                if ($out['last']) {
                                    $out['last']    .=  " $curr";
                                    }
                                else {
                                    $out['last']    =   $curr;
                                    }
                                continue;
                                }

                            if ($out['last']) {
                                $out['last']    .=  " $curr";
                                continue;
                                }

                            if ($out['middle']) {
                                $out['middle']      .=  " $curr";
                                }
                            else {
                                $out['middle']      =   $curr;
                                }
                            }                       
                        $out['suffix']  =   trim($pieces[1]);
                        break;
                    case    FALSE: // array(last,title first middles suffix)
                        $subp   =   explode(' ',trim($pieces[1]));
                        $n_subp =   count($subp);
                        for($i = 0; $i < $n_subp; $i++) {
                            $curr               =   trim($subp[$i]);
                            $next               =   trim($subp[$i+1]);

                            if ($i == 0 && in_array_norm($curr,$titles)) {
                                $out['title']   =   $curr;
                                continue;
                                }

                            if (!$out['first']) {
                                $out['first']   =   $curr;
                                continue;
                                }

                        if ($i == $n_subp-2 && $next &&
                            in_array_norm($next,$suffices)) {
                            if ($out['middle']) {
                                $out['middle']  .=  " $curr";
                                }
                            else {
                                $out['middle']  =   $curr;
                                }
                            $out['suffix']      =   $next;
                            break;
                            }

                        if ($i == $n_subp-1 && in_array_norm($curr,$suffices)) {
                            $out['suffix']      =   $curr;
                            continue;
                            }

                        if ($out['middle']) {
                            $out['middle']      .=  " $curr";
                            }
                        else {
                            $out['middle']      =   $curr;
                            }
                        }
                        $out['last']    =   $pieces[0];
                        break;
                    }
            unset($pieces);
            break;
        case    3:  // array(last,title first middles,suffix)
            $subp   =   explode(' ',trim($pieces[1]));
            $n_subp =   count($subp);
            for($i = 0; $i < $n_subp; $i++) {
                $curr               =   trim($subp[$i]);
                $next               =   trim($subp[$i+1]);
                if ($i == 0 && in_array_norm($curr,$titles)) {
                    $out['title']   =   $curr;
                    continue;
                    }

                if (!$out['first']) {
                    $out['first']   =   $curr;
                    continue;
                    }

                if ($out['middle']) {
                    $out['middle']      .=  " $curr";
                    }
                else {
                    $out['middle']      =   $curr;
                    }
                }

            $out['last']                =   trim($pieces[0]);
            $out['suffix']              =   trim($pieces[2]);
            break;
        default:    // unparseable
            unset($pieces);
            break;
        }

    return $out;
    }


?>

Самой большой проблемой, с которой я столкнулся при этом, были дела вроде «Боб Р. Смит-младший». Алгоритм, который я использовал, размещен на http://www.blackbeltcoder.com/Articles/strings/splitting-a-name-into-first-and-last-names. Мой код написан на C#, но вы можете перенести его, если вам нужен SQL.

Альтернативный простой способ - использовать parsename:

select full_name,
   parsename(replace(full_name, ' ', '.'), 3) as FirstName,
   parsename(replace(full_name, ' ', '.'), 2) as MiddleName,
   parsename(replace(full_name, ' ', '.'), 1) as LastName 
from YourTableName

источник

Это будет работать в случае, если строка FirstName / MiddleName / LastName

Select 

DISTINCT NAMES ,

   SUBSTRING(NAMES , 1, CHARINDEX(' ', NAMES) - 1) as FirstName,

   RTRIM(LTRIM(REPLACE(REPLACE(NAMES,SUBSTRING(NAMES , 1, CHARINDEX(' ', NAMES) - 1),''),REVERSE( LEFT( REVERSE(NAMES), CHARINDEX(' ', REVERSE(NAMES))-1 ) ),'')))as MiddleName,

   REVERSE( LEFT( REVERSE(NAMES), CHARINDEX(' ', REVERSE(NAMES))-1 ) ) as LastName

From TABLENAME

Отличная работа @JosephStyons и @Digs! Я использовал часть их работы для создания новой функции для SQL Server 2016 и новее. Он также обрабатывает суффиксы и префиксы.

CREATE FUNCTION [dbo].[NameParser]
(
    @name nvarchar(100)
)
RETURNS TABLE
AS
RETURN (

WITH prep AS (
    SELECT 
        original = @name,
        cleanName = REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@name)),'  ',' '),'  ',' '), '.', ''), ',', '')
)
SELECT
    prep.original,
    aux.prefix,
    firstName.firstName,
    middleName.middleName,
    lastName.lastName,
    aux.suffix
FROM
    prep
    CROSS APPLY (
        SELECT 
            prefix =
                CASE 
                    WHEN LEFT(prep.cleanName, 3) IN ('MR ', 'MS ', 'DR ', 'FR ')
                        THEN LEFT(prep.cleanName, 2)
                    WHEN LEFT(prep.cleanName, 4) IN ('MRS ', 'LRD ', 'SIR ')
                        THEN LEFT(prep.cleanName, 3)
                    WHEN LEFT(prep.cleanName, 5) IN ('LORD ', 'LADY ', 'MISS ', 'PROF ')
                        THEN LEFT(prep.cleanName, 4)
                    ELSE ''
                END,
            suffix =
                CASE 
                    WHEN RIGHT(prep.cleanName, 3) IN (' JR', ' SR', ' II', ' IV')
                        THEN RIGHT(prep.cleanName, 2)
                    WHEN RIGHT(prep.cleanName, 4) IN (' III', ' ESQ')
                        THEN RIGHT(prep.cleanName, 3)
                    ELSE ''
                END
    ) aux
    CROSS APPLY (
        SELECT
            baseName = LTRIM(RTRIM(SUBSTRING(prep.cleanName, LEN(aux.prefix) + 1, LEN(prep.cleanName) - LEN(aux.prefix) - LEN(aux.suffix)))),
            numParts = (SELECT COUNT(1) FROM STRING_SPLIT(LTRIM(RTRIM(SUBSTRING(prep.cleanName, LEN(aux.prefix) + 1, LEN(prep.cleanName) - LEN(aux.prefix) - LEN(aux.suffix)))), ' '))
    ) core
    CROSS APPLY (
        SELECT
            firstName = 
                CASE
                    WHEN core.numParts <= 1 THEN core.baseName
                    ELSE LEFT(core.baseName, CHARINDEX(' ', core.baseName, 1) - 1) 
                END

    ) firstName
    CROSS APPLY (
        SELECT
            remainder = 
                CASE
                    WHEN core.numParts <= 1 THEN ''
                    ELSE LTRIM(SUBSTRING(core.baseName, LEN(firstName.firstName) + 1, 999999))
                END
    ) work1
    CROSS APPLY (
        SELECT
            middleName = 
                CASE
                    WHEN core.numParts <= 2 THEN ''
                    ELSE LEFT(work1.remainder, CHARINDEX(' ', work1.remainder, 1) - 1) 
                END
    ) middleName
    CROSS APPLY (
        SELECT
            lastName = 
                CASE
                    WHEN core.numParts <= 1 THEN ''
                    ELSE LTRIM(SUBSTRING(work1.remainder, LEN(middleName.middleName) + 1, 999999))
                END
    ) lastName
)

GO

SELECT * FROM dbo.NameParser('Madonna')
SELECT * FROM dbo.NameParser('Will Smith')
SELECT * FROM dbo.NameParser('Neil Degrasse Tyson')
SELECT * FROM dbo.NameParser('Dr. Neil Degrasse Tyson')
SELECT * FROM dbo.NameParser('Mr. Hyde')
SELECT * FROM dbo.NameParser('Mrs. Thurston Howell, III')

Проверьте этот запрос в Athena только на наличие строки, разделенной одним пробелом (например, комбинацию имени и отчества):

SELECT name, REVERSE( SUBSTR( REVERSE(name), 1, STRPOS(REVERSE(name), ' ') ) ) AS middle_name FROM name_table

Если вы ожидаете наличия двух или более пробелов, вы можете легко расширить вышеуказанный запрос.

Основываясь на вкладе @ hajili (который представляет собой творческое использование функции parsename, предназначенной для анализа имени объекта, разделенного точкой), я изменил его, чтобы он мог обрабатывать случаи, когда данные не содержат отчество или когда зовут «Джон и Джейн Доу». Он не идеален на 100%, но он компактен и может помочь в зависимости от бизнес-модели.

SELECT NAME,
CASE WHEN parsename(replace(NAME, ' ', '.'), 4) IS NOT NULL THEN 
   parsename(replace(NAME, ' ', '.'), 4) ELSE
    CASE WHEN parsename(replace(NAME, ' ', '.'), 3) IS NOT NULL THEN 
    parsename(replace(NAME, ' ', '.'), 3) ELSE
   parsename(replace(NAME, ' ', '.'), 2) end END as FirstName
   ,
CASE WHEN parsename(replace(NAME, ' ', '.'), 3) IS NOT NULL THEN 
   parsename(replace(NAME, ' ', '.'), 2) ELSE NULL END as MiddleName,
   parsename(replace(NAME, ' ', '.'), 1) as LastName
from  {@YourTableName}

Этот запрос работает нормально.

SELECT name
    ,Ltrim(SubString(name, 1, Isnull(Nullif (CHARINDEX(' ', name), 0), 1000))) AS FirstName
    ,Ltrim(SUBSTRING(name, CharIndex(' ', name), CASE 
                WHEN (CHARINDEX(' ', name, CHARINDEX(' ', name) + 1) - CHARINDEX(' ', name)) <= 0
                    THEN 0
                ELSE CHARINDEX(' ', name, CHARINDEX(' ', name) + 1) - CHARINDEX(' ', name)
                END)) AS MiddleName
    ,Ltrim(SUBSTRING(name, Isnull(Nullif (CHARINDEX(' ', name, Charindex(' ', name) + 1), 0), CHARINDEX(' ', name)), CASE 
                WHEN Charindex(' ', name) = 0
                    THEN 0
                ELSE LEN(name)
                END)) AS LastName
FROM yourtableName

В таблице сотрудников есть столбец «Имя», и нам пришлось разделить его на имя, отчество и фамилию. Этот запрос будет обрабатывать отчество как нулевое, если столбец имени имеет значение из двух слов, например «Джеймс Томас».

UPDATE Employees
SET [First Name] = CASE 
        WHEN (len(name) - len(Replace(name, '.', ''))) = 2
            THEN PARSENAME(Name, 3)
        WHEN (len(name) - len(Replace(name, '.', ''))) = 1
            THEN PARSENAME(Name, 2)
        ELSE PARSENAME(Name, 1)
        END
    ,[Middle Name] = CASE 
        WHEN (len(name) - len(Replace(name, '.', ''))) = 2
            THEN PARSENAME(Name, 2)
        ELSE NULL
        END
    ,[Last Name] = CASE 
        WHEN (len(name) - len(Replace(name, '.', ''))) = 2
            THEN PARSENAME(Name, 1)
        WHEN (len(name) - len(Replace(name, '.', ''))) = 1
            THEN PARSENAME(Name, 1)
        ELSE NULL
        END GO

UPDATE Employee
SET [Name] = Replace([Name], '.', ' ') GO