t-sql working with string list of fullnames

Using SQL Server 2008

This is a follow up to this example:
https://www.experts-exchange.com/questions/29134468/t-sql-display-query-result-as-list-of-comma-separated-values.html?anchor=a42789277¬ificationFollowed=222867761&anchorAnswerId=42789277#a42789277

In that example

I had this table:

Use [Northwind]
create table Employee (EmployeeID int, LastName varchar(50))
insert into Employee values (1,'Davolio'), (2,'Fuller'), (3,'Leverling'), (4, 'Peacock'), (5,'Buchanan'), (6, 'Suyama'),(7,'King'),(8,'John'),(9,'Peter')

Select allOpen in new window

The input was a comma separated string list of LastName

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio,Fuller,Leverling,Peacock,Buchanan,Suyama,King,Callahan,Dodsworth'
------------- Input -------------


The solution was this query. The output of this query is a list of EmployeeIDs of those last names.

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio,Fuller,Leverling,Peacock,Buchanan,Suyama,King,Callahan,Dodsworth'
------------- Input -------------
------------- Query -------------
;with cte1 as (
select ltrim(SUBSTRING(LastNames, n, CHARINDEX(',', LastNames + ',',n) - n)) AS LastName
  from (select @LastName as LastNames) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING(',' + LastNames, n, 1) = ','
  AND n < LEN(LastNames) + 1) --select * from cte1
select distinct rtrim(substring(isnull((select ','+convert(varchar,EmployeeID) 
                                          from Employee t1 
                                          join cte1 t2
                                            on t1.LastName = t2.LastName 
                                           for xml path('')),' '),2,2000)) EmployeeIDs
------------- Query -------------
------------- Output -------------
-------------
/*
  	EmployeeIDs
	1,2,3,4,5,6,7
*/
------------- Output -------------

Select allOpen in new window

New Query for FullName list input

Now if I have a table that looks like this:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (1, N'Davolio', N'Nancy')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (2, N'Fuller', N'Andrew')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (3, N'Leverling', N'Janet')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (4, N'Peacock', N'Margaret')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (5, N'Buchanan', N'Steven')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (6, N'Suyama', N'Michael')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (7, N'King', N'Robert')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (8, N'Callahan', N'Laura')
GO
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName]) VALUES (9, N'Dodsworth', N'Anne')
GO

Select allOpen in new window

Now if my input is a string list of [LastName] + ' ' + FirstName] like this:

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio Nancy,Fuller Andrew,Leverling Janet'
------------- Input -------------

How do I revise the query below, to get the employee ids for those 3 employees. So the output would be:

      EmployeeIDs
      1,2,3

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio Nancy,Fuller Andrew,Leverling Janet'
------------- Input -------------
------------- Query -------------
;with cte1 as (
select ltrim(SUBSTRING(LastNames, n, CHARINDEX(',', LastNames + ',',n) - n)) AS LastName
  from (select @LastName as LastNames) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING(',' + LastNames, n, 1) = ','
  AND n < LEN(LastNames) + 1) --select * from cte1
select distinct rtrim(substring(isnull((select ','+convert(varchar,EmployeeID) 
                                          from [TestDatabase].[dbo].[Employees] t1 
                                          join cte1 t2
                                            on t1.LastName = t2.LastName 
                                           for xml path('')),' '),2,2000)) EmployeeIDs
------------- Query -------------

Select allOpen in new window