提取名字和姓氏 [英] Fetching first name and last name

问题描述

我在表中有一个全名的数据.这些名称包含姓氏，名字中间名"，我需要从全名中提取名字，姓氏和中间名并将它们存储在不同的字段中.

I have a data in tables as full name. These name contains "Last_Name, First Name Middle Name" I need to fetch first name, last name and middle name from full name and store them in different fields.

例如，我在表中的全名是-

For example, I have full names in table as -

• 威廉姆斯，罗伯特·克

• Williams, Robert K

温彻斯特(Sam T)

Winchester, Sam T

我需要将它们存储为

FirstName        Last_Name       MName
Robert            Williams         K
Sam               Winchester       T

我正在女士访问权限的某些表上执行此操作.谁能帮我实现这个目标.

I am doing it on some tables in Ms access. Can anybody please help me how I can achieve this.

推荐答案

以下是非VBA解决方案的尝试:

Here's an attempt at a non-VBA solution:

select
    iif(t.name like "*,*", trim(mid(t.name, instr(t.name, ",")+1, len(t.name) - instr(t.name, ",") - iif(t.name like "* ?", 2, 0))), iif(t.name like "* ?", left(t.name, len(t.name)-2), t.name)) as FName, 
    iif(t.name like "*,*", left(t.name, instr(t.name, ",")-1), Null) as LName,
    iif(trim(t.name) like "* ?", right(trim(t.name), 1), Null) as MName
from
    YourTable t

更改YourTable以适合您的表名.

• 如果没有逗号，则表示该名称是名字(可能是中间名).

• The absence of a comma implies that the name is a first name (and possible middle-initial).

假定中间的首字母始终为1个字符

Middle initials are assumed to always be 1 character

给出示例数据:

+--------------------+
|        Name        |
+--------------------+
| Williams, Robert K |
| Winchester, Sam T  |
| Smith, John        |
| Jack A             |
| Harry              |
| ,Thomas T          |
+--------------------+

上面的查询将产生:

+--------+------------+-------+
| FName  |   LName    | MName |
+--------+------------+-------+
| Robert | Williams   | K     |
| Sam    | Winchester | T     |
| John   | Smith      |       |
| Jack   |            | A     |
| Harry  |            |       |
| Thomas |            | T     |
+--------+------------+-------+

这篇关于提取名字和姓氏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！