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

maqskywalker used Ask the Experts™
This is a followup question to this example


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 (
  ,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');

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,'

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,'

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?

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

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Doug BishopDatabase Developer

  like n.sID + '%
  like '%' + n.sID + '%

Note that this will not use any index you might have on FullName and will result in doing a full table scan. If your table is large, this could take some time to complete, but should complete fairly quick for up to 50K rows.
Database Developer
Also, if you want to match the start of either the first or last name (e.g. 'smi' will match Bob Smith but not Jim Bosmin and your names are always formatted as FirstName + ' ' + LastName you can change the JOIN condition to:
t.fullname LIKE n.sID + '% OR t.fullname LIKE '% ' + n.sID + '%'
Note the space after the second %.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial