CODEWITHSUNDEEP
sql-server-2008ssis
iims
iims

Reputation: 313

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

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?

Upvotes: 2

Views: 5950

Answers (3)

user10344256
user10344256

Reputation: 1

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

Upvotes: 0

user756519
user756519

Reputation:

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

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Upvotes: 3

Related Questions