Partial String Search from Subquery

Question

Post reply

Partial String Search from Subquery

lenardd

Default port

Points: 1435
More actions
December 14, 2007 at 3:45 pm

#62602

Hi All,
Assume the following:
declare @search varchar(50)
set @search = 'Johnny, Maryanne'
declare @searchParts table ( strval varchar(50) not null )
INSERT INTO @searchParts (SELECT strval FROM dbo.StrListToTable( @search )
SELECT
Person.ID,
Person.FirstName,
Person.LastName
FROM
Person
WHERE
Person.FirstName IN ( SELECT * FROM @searchParts ) OR
Person.LastName IN ( SELECT * FROM @searchParts )
My question is, how would I convert the query so I could do partial searches using @searchParts? For example, I would like to set @search = 'John%, Mary%'. I'm not sure how to use LIKE in this senario.
Thanks.
--Lenard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

lenardd Default port Points: 1435 More actions · Answer 1

Well, after going for a nice long freezing walk, I figured it out:

WHERE

EXISTS ( SELECT * FROM @nameParts WHERE Person.FirstName LIKE strval )

OR EXISTS ( SELECT * FROM @nameParts WHERE Person.LastName LIKE strval )

OR EXISTS ( SELECT * FROM @nameParts WHERE Person.MiddleName LIKE strval ) )

Not sure if it's the most efficient way, but it seems to work.

--Lenard

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You could try these as an alternative:

SELECT

p.ID,

p.FirstName,

p.LastName

FROM Person p

INNER JOIN @searchParts s

ON (p.FirstName LIKE s.strval + '%'

OR p.LastName LIKE s.strval + '%')

Alternatively:

SELECT

p.ID,

p.FirstName,

p.LastName

FROM Person p

LEFT JOIN @searchParts s1

ON p.FirstName LIKE s1.strval + "%"

LEFT JOIN @searchParts s2

ON p.LastName LIKE s2.strval + "%"

WHERE s1.strval IS NOT NULL OR s2.strval IS NOT NULL

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lenardd Default port Points: 1435 More actions · Answer 3

Thanks Chris.

Never thought of using LIKE in the join. I think your method would be a little more efficient too.

--Lenard

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Welcome, Lenard. Good luck!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden