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');
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
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.