自己取名字和姓氏

Question

我需要帮助来分别获得名字和姓氏。

字段为[全名]：Halsey S Dunn

我可以单独获得名字，但无法单独获得姓氏：

这是我的名字和姓氏代码：

,SUBSTRING([FullName], 1, CHARINDEX(' ', [FullName]) - 1) AS [FirstName]
,SUBSTRING(
            [FullName],1 + CHARINDEX(' ', [FullName]),LEN([FullName])) as [LastName]

我的结果需要是：

FirstName- Halsey
LastName- Dunn

Answer 1

假设您正在使用SQL服务器，您可以尝试以下操作：

示例

create table people (fullname varchar(100));
insert into people values ('Harvey S Dunn');
insert into people values ('Tony Mony');

SQL

SELECT 

   left(fullname, charindex(' ', fullname)) as firstname,

   reverse(
     right(
        reverse(fullname),
        len(fullname) - nullif(charindex(' ', reverse(fullname)),0)
     )
   ) as fullname_without_lastname,
      
   reverse(
      left(
        reverse(fullname),
        charindex(' ', reverse(fullname)) - 1
      )
   ) as lastname
      
from people;

后果