Can You Search a Single Field on Multiple Patterns?
-
I am trying to figure out a way to write a query with only one LIKE or PATINDEX statement in the Where clause that can return records based on whether the data in a single field matches one of several different patterns. I am using SQL Server 2000.
For instance, if we are dealing with a last name field that contains the following data:
Smith
Jones
Perkins
Smithers
Holt
I am looking for is a single statement that can return the records where the last name starts with either SMIT or PER, in this case returning:
Smith
Perkins
Smithers
What I would like is to have is code somewhat like this :
WHERE LastName LIKE '(SMIT|PER)%' -- (SMIT|PER) means "SMIT or PER")
not like this:
WHERE LastName LIKE 'SMIT%' OR LastName LIKE 'PER%'
Ultimately, this query will be built dynamically because there could be any number of different patterns to search on.
It seems like PATINDEX and LIKE can only test on one pattern at a time. I know you can set regular expressions to do this, but it doesn't seem to work within PATINDEX. I have tried using a UDF that calls the external VBScript.RegExp object, but performace is really bad.
Does anyone have any ideas?
Thanks,
Mark
-
Read up on 'Full-Text Search' in books online
Once it's set up, you can do things like...
WHERE CONTAINS(LastName, ' "SMIT*" OR "PER*" ')
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Ryan,
I have never used full-text search and did not even think about it. I set it up and tried your suggestion and it works like a charm.
Thanks for your help.
Mark
-
You can also put the candidate values in a table and join to it if FTS is not an option.
Take care..- Ward Pond
blogs.technet.com/wardpond
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply