t-sql filter string column for partial lastname or partial firstname

This is a followup question to this example

https://www.experts-exchange.com/questions/29134212/sql-server-filter-by-partial-string.html?anchor=a42787229¬ificationFollowed=222694999#a42787229

In that example I used a split string function called udfSplitString

Then I searched multiple partial lastnames in a comma separted list


Now what if my table looks like this:

CREATE TABLE mytable2 (
   id       INTEGER  NOT NULL PRIMARY KEY 
  ,fullname VARCHAR(20)
);
INSERT INTO mytable2(id,fullname) VALUES (1,'Nancy Davolio');
INSERT INTO mytable2(id,fullname) VALUES (2,'Andrew Fuller');
INSERT INTO mytable2(id,fullname) VALUES (3,'Robert King');
INSERT INTO mytable2(id,fullname) VALUES (4,'Janet Leverling');
INSERT INTO mytable2(id,fullname) VALUES (5,'Steven Buchanan');

Select all Open in new window

I would like to filter the fullname column by partial first name or partial lastname

If my input is this

-- input 1 - partial lastname
DECLARE @EmployeeName varchar(2500)
SET @EmployeeName = 'Dav,Ful,Kin,'

Select all Open in new window

I would get the records for Nancy Davolio, Andrew Fuller and Robert King

If my input is this:

-- input 2 - partial firstname
DECLARE @EmployeeName varchar(2500)
SET @EmployeeName = 'Jan,Stev,Andre,'

Select all Open in new window

I would get the records for Janet Leverling, Steven Buchanan and Andrew Fuller

How would I revises this statement to input either partial firstname or lastname and look in the fullname column for those?

select
    t.*
from mytable2 t
inner join (
    select sID
    from dbo.udfSplitString(@EmployeeName)
    ) n on t.fullname like n.sID + '%'

Select all Open in new window