sql - how to split fullname into first and last na

问题:

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

fullname
------------
Joe Bloggs
Peter Smith
Mary Jones and 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.

So basically to retrieve

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

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

回答1:

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

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.).

回答2:

This is a slippery slope and there are no easy answers. That said, consider the following

Declare @YourTable table (FullName varchar(50))
Insert Into @YourTable values
('Joe Bloggs'),
('Peter Smith'),
('Betty Jane Martinez'),
('Mary Jones and Liz Stone')


Select A.*
      ,FirstName = Pos1+case when Pos3 is not null then ' '+Pos2 else '' end
      ,LastName  = case when Pos3 is null then Pos2 else Pos3 end
 From @YourTable A
 Cross Apply (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
     From  (Select Cast('<x>' + replace((Select substring(FullName,1,charindex(' and ',FullName+' and ')-1) as [*] For XML Path('')),' ','</x><x>')+'</x>' as xml) as xDim) as A 
 ) B

Returns

FullName                    FirstName   LastName
Joe Bloggs                  Joe         Bloggs
Peter Smith                 Peter       Smith
Betty Jane Martinez         Betty Jane  Martinez
Mary Jones and Liz Stone    Mary        Jones

If it helps with the visual, the CROSS APPLY generates

回答3:

SELECT CASE
           WHEN CHARINDEX(' ', FullName) > 0
           THEN SUBSTRING(FullName, 1, LEN(FullName) - CHARINDEX(' ', REVERSE(FullName)))
           ELSE ''
       END AS FirstName,
       CASE
           WHEN CHARINDEX(' ', FullName) > 0
           THEN REVERSE(SUBSTRING(REVERSE(FullName), 
                       1, 
                       CHARINDEX(' ', REVERSE(FullName)) - 1))
           ELSE FullName
       END AS LastName
FROM(VALUES('Mary Anne Bloggs'), ('Joe Bloggs'), ('Bloggs')) AS T(FullName);

This version checks that there is a space in the full name to split on. If there isn't then the first name is set to an empty string and the full name is put into the surname. Also, reverse is employed to split on the last space when there is more than one space

回答4:

This is the easiest and shortest to this question without any assumptions. Also you can even further enhance this with a rtrim(ltrim('firstname lastname')).

Just in case of any spaces before the strings,

Select 
    substring('Firstname Lastname',1,CHARINDEX(' ', 'Firstname Lastname')) as firstname, 
    substring('Firstname Lastname',CHARINDEX(' ', 'Firstname Lastname'),LEN('Firstname Lastname')) as Lastname

回答5:

I use this query to retrive first and lastname

SELECT SUBSTRING(FULLNAME, 1, CASE WHEN CHARINDEX(' ', FULLNAME)>0 THEN CHARINDEX(' ', FULLNAME) - 1 ELSE LEN(FULLNAME) END ) AS Firstname,
  REVERSE(SUBSTRING(REVERSE(FULLNAME), 1, CASE WHEN CHARINDEX(' ', REVERSE(FULLNAME))>0 THEN CHARINDEX(' ', REVERSE(FULLNAME)) - 1 ELSE LEN(REVERSE(FULLNAME)) END ) )AS Firstname FROM  HRMDESFO.EMPLOID

Resuls

回答6:

select  passemail,substring(passemail,1,instr(passemail,'@') - 1) as name ,
substring(passemail,instr(passemail,'@') + 1,length(passemail)) from passenger

回答7:

BigQuery: Standard Sql

substr(name,1,STRPOS(name,' ')-1) as FirstName,

substr(name,STRPOS(name,' ')+1,length(name)) as LastName

回答8:

You can use,

 STRING_SPLIT (string , separator)