troubleshooting Question

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

Avatar of maqskywalker
maqskywalker asked on
Microsoft SQL Server
2 Comments1 Solution112 ViewsLast Modified:
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');

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

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

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 + '%'
ASKER CERTIFIED SOLUTION
D B
Database Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros