Avatar of maqskywalker
maqskywalker
 asked on

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&anchorAnswerId=42787229#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');

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?

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

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
D B

8/22/2022 - Mon
D B

Change:
  like n.sID + '%
to  
  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.
ASKER CERTIFIED SOLUTION
D B

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy