SQL：解析全名字段中的名字，中间名和姓氏
时间：2020-03-06 14:59:48 来源:igfitidea点击:
如何使用SQL从全名字段中解析名字，中间名和姓氏？
我需要尝试匹配与全名不直接匹配的名称。我希望能够使用全名字段并将其分解为名字，中间名和姓氏。
数据不包含任何前缀或者后缀。中间名称是可选的。数据的格式为" First Middle Last"。
我对一些实用的解决方案感兴趣，可以让我90％地达到目标。如前所述，这是一个复杂的问题，因此我将分别处理特殊情况。
解决方案
除非我们拥有非常非常完善的数据，否则这将是一个不小的挑战。天真的方法是对空白进行标记化，并假设三标记结果为[first，middle，last]，而两标记结果为[first，last]，但是我们将不得不处理多标记单词姓氏(例如" Van Buren")和多个中间名。
我们确定法定全名将始终包含名字，中间名和姓氏吗？我知道只有全名的人，老实说，我不确定这是他们的名字还是姓氏。 :-)我也知道在法定名称中有多个Fisrt名称但没有中间名称的人。有些人有多个中间名。
在合法全名中，还有名称的顺序。据我所知，在某些亚洲文化中，姓氏在全名中名列第一。
在更实际的说明中，我们可以在空白处拆分全名，并威胁将第一个标记作为名字，将最后一个标记(或者只有一个名称的情况下，唯一的标记)作为姓氏威胁。尽管这假设顺序将始终相同。
- 获取一个sql regex函数。范例：http：//msdn.microsoft.com/en-us/magazine/cc163473.aspx
- 使用正则表达式提取名称。
我推荐Expresso用于学习/构建/测试正则表达式。旧的免费版本，新的商业版本
不知道"全名"的格式是很难回答的。
可以是"姓氏，名字中间名"或者"名字中间名姓氏"等。
基本上，我们必须使用SUBSTRING函数
SUBSTRING ( expression , start , length )
可能是CHARINDEX函数
CHARINDEX (substr, expression)
找出要提取的每个部分的起点和长度。
因此，我们可以说格式为" First First Last Name"(未测试..但应接近)：
SELECT
SUBSTR(fullname, 1, CHARINDEX(' ', fullname) - 1) AS FirstName,
SUBSTR(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 Server，但是在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
regex表达式可能更简洁一些。但是你明白了。顺便说一句，这对于具有两个重复名称的人是行不通的(在荷兰，我们有很多" Jan van der Ploeg")，所以我对结果要非常小心。
就像＃1所说的那样，这并非微不足道。带有连字符的姓，缩写，双名，逆名序列和其他各种异常现象可能会破坏我们精心设计的功能。
我们可以使用第3方库(我在此产品上使用的插件/免责声明)：
http://www.melissadata.com/nameobject/nameobject.htm
我将其作为一个迭代过程进行。
1)将表转储到平面文件中进行处理。
2)编写一个简单的程序，使用空格作为分隔符来分解姓名，其中firsts标记是名字，如果有3个标记，则标记2是中间名称，标记3是姓氏。如果有2个标记，则第二个标记为姓。 (Perl，Java或者C / C ++，语言无关紧要)
3)眼球结果。查找不符合此规则的名称。
4)使用该示例，创建一个新规则来处理该异常...
5)冲洗并重复
最终，我们将获得一个修复所有数据的程序。
这是一个独立的示例，其中包含易于操作的测试数据。
在此示例中，如果名称包含三个以上的部分，那么所有"多余"的内容都会放入LAST_NAME字段中。对标识为"标题"的特定字符串(例如" DR"，" MRS"和" MR")进行例外处理。
如果缺少中间名，则只得到FIRST_NAME和LAST_NAME(MIDDLE_NAME为NULL)。
我们可以将其粉碎到一个巨大的SUBSTRING嵌套嵌套Blob中，但是可读性已经足够困难，就像在SQL中这样做一样。
编辑处理以下特殊情况：
1 NAME字段为NULL
2 NAME字段包含前导/尾随空格
3名称字段在名称中具有> 1个连续空格
4 NAME字段仅包含名字
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
就像其他人说的那样，我们无法通过简单的编程方式来实现。
考虑以下示例：
- 总统"乔治·赫伯特·沃克·布什"(第一中中下)
- 总统刺客"约翰·威尔克斯·布斯"(第一中场)
- 吉他手" Eddie Van Halen"(倒数第一)
- 而他的妈妈可能称他为爱德华·洛德维克·范·哈伦(Edward Lodewijk Van Halen)
- 著名的漂流者"玛丽·安·萨默斯(Mary Ann Summers)"(先到后)
- 新墨西哥州共和党主席" Fernando C de Baca"(倒数第二个)
我们当然都知道，没有完美的方法可以解决此问题，但是某些解决方案可以使我们走得更远。
特别是，如果我们仅具有一些常用前缀(Mr，Dr，Mrs等)，中缀(von，de，del等)，后缀(Jr，III)的列表，则可以轻松超越简单的空格分隔符，Sr等)。如果我们有一些常用名字的列表(如果使用不同的语言/文化，如果名字多种多样)，这也很有用，这样我们就可以猜测中间的单词是否可能是姓氏的一部分。
BibTeX还实现了一些启发式方法，可以融入其中。它们封装在Text :: BibTeX :: Nameperl模块中。这是一个可以完成合理工作的快速代码示例。
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解析人名，则建议使用Keith Beckman的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;
}
?>
