sql - 如何将全名拆分为名字和姓氏 [英] sql - how to split fullname into first and last name

问题描述

如果我有一个包含全名的列的表，例如:

If I have a table with a column that contains fullnames such as :

fullname
------------
Joe Bloggs
Peter Smith
Mary Jones and Liz Stone

如何使用 SQL 从全名列中的每个条目中重试名字和姓氏.我不担心示例中第三个条目中的第二个名字，即 Liz Stone.

How can I retried the first and last name from each of the entries in the full name column using SQL. I'm not worried about the second name in the 3rd entry in my example i.e. Liz Stone.

所以基本上是检索

Firstname
---------
Joe
Peter
Mary

Lastname  
--------
Bloggs 
Smith
Jones

推荐答案

这里是 SQL Server 2016 之前的方法，它使用基本的字符串函数来隔离名字和姓氏.

Here is a pre SQL Server 2016 method, which uses basic string functions to isolate the first and last names.

SELECT SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) AS Firstname,     
       SUBSTRING(fullname,
                 CHARINDEX(' ', fullname) + 1,
                 LEN(fullname) - CHARINDEX(' ', fullname)) AS Lastname
FROM yourTable

请注意，此解决方案假定 fullname 列仅包含一个名字和一个姓氏(即没有中间名、首字母缩写等).

Note that this solution assumes that the fullname column only contains a single first name and a single last name (i.e. no middle names, initials, etc.).

这篇关于sql - 如何将全名拆分为名字和姓氏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！