Filter recordset with comma delimited search criteria

Hi Experts,

Can you help me out with a solution for creating an MSSQL Server stored procedure that will return a list of records filtered by a comma delimited list of criteria?

I’d like a user to be able enter their search criteria as a comma delimited string. Each delimited value can be value contained within a fixed set of fields within a recordset. The order of the delimited values are randomly placed, i.e. it could be ‘London,British,Lewis or they could just as well be ‘British,Lewis, London’

The recordset will contain among other fields the following 3 fields to be searched, City, Ethnicity and LastName.

RecID      City      Ethnicity      LastName
1001      Birmingham      Black British      Lewis
1002      London      White British      Peters
1003      London      Chinese      Han Seng
1004      Cardiff      Asian British      Singh
1005      London      Asian British      Lewis-Patel
1006      London      White British      Lewis-Morgan

I’d like the user to enter something like London,British,Lewis

The 3 delimited values will determine 3 search loops.

Loop 1 will search the 3 fields of the entire recordset for any value containing the word  ‘London  this will return 1002,1003,1005,1006

Loop 2 will search the records pre-filtered from Loop1 for any value containing the word  ‘British’  this will return 1002, 1005,1006

Loop 3 will search the records pre-filtered from Loop2 for any value containing the word  ‘Lewis  this will return 1005,1006 which then returns those records to the user.

I have got all this set up in VBA with ADO and it works great but I think it will be far more efficient if it was done directly using TSQL.

Any help or pointers will be greatly appreciated and thanks in advance for your valuable time.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:

What do you actually want to return from the stored query?

Would it only be 1005 and 1006?
dataflowjoeAuthor Commented:
Hi there,,

I'd like to return either all the fields or just a selection of fields.
These will be displayed to the user in a selection list, the primary key field will need to be returned but can be hidden from the user. The RecID will be used after the user has made their selection from the records returned in the list.

SharathData EngineerCommented:
try this query.
DECLARE @string VARCHAR(500) 

SELECT @string = 'London,British,Lewis' 

SELECT t1.* 
  FROM your_table t1 
       JOIN (SELECT LTRIM(SUBSTRING(ip_string, n, CHARINDEX(',', ip_string + ',', n) - n)) AS ip_string
               FROM (SELECT @string ip_string) t1 
                    CROSS JOIN (SELECT number 
                                  FROM master..spt_values 
                                 WHERE type = 'P') AS Numbers(n) 
              WHERE SUBSTRING(',' + ip_string, n, 1) = ',' 
                AND n < LEN(ip_string) + 1) t2 
         ON t2.ip_string IN ( t1.City, t1.Ethnicity, t1.LastName ) 

Open in new window

Here is the sample tested:!3/5f4ba/2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dataflowjoeAuthor Commented:
Hi Sharath_123,

I do apologise for not getting back to you sooner.
I really appreciate the trouble you have gone to in answering my question.
I've had a look at your sqlfiddle and it doesn't do what I was expecting to happen. The resulting recordset should only be showing RecId's 1005 and 1006 because they contain, London, British and Lewis.


dataflowjoeAuthor Commented:
Thanks for your trouble, it didn't quite do what I expected but the principle is there.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.