How to split full name in a variable to first and last names within SQL Server?

Question

I have an SQL Server stored procedure which reads from a csv using SSIS and populates data in different tables in SQL Server 2008.

The issue that I have is that in the csv, I have a column called name, which I pass into the variable @name but this name has first and last name separated by a space.

Sample value:

@name = 'sullivian white'

However, this single variable value has to be saved into the database under two columns, named firstname and last name.

How can I extract the data? Is there a way to use substring to split this variable?

Probably worth looking at JosephStyons' [excellent answer](http://stackoverflow.com/questions/159567/how-can-i-parse-the-first-middle-and-last-name-from-a-full-name-field-in-sql/159760#159760) which covers a ton of cases. EfficionDave Turned it into a function [here](http://www.efficionconsulting.com/Blog/itemid/643/amid/1500/sql-function-to-parse-first-name-from-full-name.aspx) — Conrad Frix, May 23 '11 at 21:23

score 3 · Accepted Answer · answered May 23 '11 at 20:15

3

You can use substring and charindex

declare @name varchar(50)
set @name = 'sullivian white'

select
  substring(@name, 1, charindex(' ', @name)-1),
  substring(@name, charindex(' ', @name)+1, len(@name))

answered May 23 '11 at 20:15

Mikael Eriksson

136,425
22
210
281

1

If you're dealing with any sizable amount of data then you'll likely have to account for various possibilities, such as a name of "John Van Bergen" or "Mary Kay Williams" – Tom H May 23 '11 at 20:31

score 2 · Answer 2 · 2013-03-01T05:39:33.757

I assumed that last name will have only one entity but first name could have multiple entities.

Query against a variable value:

DECLARE @Name       NVARCHAR(60)
DECLARE @LastSpace  INT

SET @Name       = 'Mary Kay Williams'
SET @LastSpace  = CHARINDEX(' ', REVERSE(@Name))

SELECT  @Name   AS FullName
    ,   SUBSTRING(@Name, 1, LEN(@Name) - @LastSpace)                AS FirstName
    ,   SUBSTRING(@Name, LEN(@Name) - @LastSpace + 1, @LastSpace)   AS LastName

Query against a set:

SELECT Name,
        SUBSTRING(      Name
                    ,   1
                    ,   LEN(Name) - CHARINDEX(' ', REVERSE(Name))
                ) AS FirstName,
        SUBSTRING(      Name
                    ,   LEN(Name) - CHARINDEX(' ', REVERSE(Name)) + 1
                    ,   CHARINDEX(' ', REVERSE(Name))
                ) AS LastName
FROM
(
    SELECT 'Sullivan White'     Name UNION
    SELECT 'John Van Bergen'    Name UNION
    SELECT 'Mark Kay Williams'  Name
) Names

score 0 · Answer 3 · edited Sep 11 '18 at 01:00

Imagine the name has three or four spaces. I have found a solution thanks to Tek-Tips

//The way to extract name depends on how much separations does the name have.
DECLARE @NOMBRE VARCHAR(50) = 'YOCASTA DE LA MOTA FERNANDEZ CAMARENA'
declare @Name varchar(50)
declare @LastName varchar(50)

SELECT 
@Name = SUBSTRING(@NOMBRE,0,CHARINDEX(' ', @NOMBRE,  CHARINDEX(' ', @NOMBRE,    CHARINDEX(' ',@NOMBRE,CHARINDEX(' ',@NOMBRE)+1) + 1) + 1))
,@LastName =  SUBSTRING(@NOMBRE,
CHARINDEX(' ', @NOMBRE,
  CHARINDEX(' ', @NOMBRE,
    CHARINDEX(' ',@NOMBRE,CHARINDEX(' ',@NOMBRE)+1) + 1) + 1),LEN(@NOMBRE))

    select @Name,@LastName

How to split full name in a variable to first and last names within SQL Server?

Sample value:

3 Answers3

Query against a variable value:

Query against a set: