Filter recordset with comma delimited search criteria

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-11-22
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.

Question by:dataflowjoe
  • 3
LVL 37

Expert Comment

ID: 39571102

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

Would it only be 1005 and 1006?

Author Comment

ID: 39571148
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.

LVL 41

Accepted Solution

Sharath earned 1500 total points
ID: 39581512
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: http://sqlfiddle.com/#!3/5f4ba/2

Author Comment

ID: 39638470
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.



Author Closing Comment

ID: 39669594
Thanks for your trouble, it didn't quite do what I expected but the principle is there.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question