Link to home
Create AccountLog in
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

Avatar of D B
D B
Flag of United States of America image

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
Avatar of D B
D B
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account