Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filter recordset with comma delimited search criteria Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 1:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 25, 2013 3:02 PM
Points: 2, Visits: 16
Hi TSQL'ers,

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 as the search criteria.

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 RecId's 1002,1003,1005,1006

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

Loop 3 will search the records pre-filtered from Loop2 for any value containing the word ‘Lewis this will return RecId's 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.

Joe
Post #1505096
Posted Wednesday, October 16, 2013 2:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 2,422, Visits: 7,442
Bit difficult to see exactly what you want to do, so I'm going to take a stab in the dark.

First, it's always a good idea to set up readily consumable sample data for people to make it easier to help you. In your case, this is enough: -
-- Set up readily consumable sample data so that we can test any solution
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT RecID,City,Ethnicity,LastName
INTO #testEnvironment
FROM (VALUES(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')
)a(RecID,City,Ethnicity,LastName);

Now anyone can execute that and have exactly the setup that you want them to test with.

That out of the way, here is what I think you're trying to do: -
-- Actual solution starts here
DECLARE @Input VARCHAR(8000) = 'Birmingham,Lewis,British';

SELECT RecID,City,Ethnicity,LastName,
MAX(CASE WHEN City LIKE '%'+Item+'%' THEN 'Matches City' ELSE '' END),
MAX(CASE WHEN Ethnicity LIKE '%'+Item+'%' THEN 'Matches Ethnicity' ELSE '' END),
MAX(CASE WHEN LastName LIKE '%'+Item+'%' THEN 'Matches LastName' ELSE '' END)
FROM #testEnvironment
CROSS APPLY [dbo].[DelimitedSplit8K](@Input,',')
WHERE CASE WHEN City LIKE '%'+Item+'%' THEN City END IS NOT NULL OR
CASE WHEN Ethnicity LIKE '%'+Item+'%' THEN Ethnicity END IS NOT NULL OR
CASE WHEN LastName LIKE '%'+Item+'%' THEN LastName END IS NOT NULL
GROUP BY RecID,City,Ethnicity,LastName;

Which returns: -
RecID       City       Ethnicity     LastName                                    
----------- ---------- ------------- ------------ ------------ ----------------- ----------------
1001 Birmingham Black British Lewis Matches City Matches Ethnicity Matches LastName
1002 London White British Peters Matches Ethnicity
1004 Cardiff Asian British Singh Matches Ethnicity
1005 London Asian British Lewis-Patel Matches Ethnicity Matches LastName
1006 London White British Lewis-Morgan Matches Ethnicity Matches LastName


This solution requires the 8K String Splitter (article here --> http://www.sqlservercentral.com/articles/Tally+Table/72993/) : -
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1505111
Posted Thursday, October 17, 2013 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 25, 2013 3:02 PM
Points: 2, Visits: 16
Hi Cadavre,

I don't know how to thank you enough for such a comprehensive answer, this is exactly what I was looking for.
I'm picking through it bit by bit to understand the logic of it all.
In future I will add some meaningful sample data as a script to make it easier for a working solution.

Greatly appreciated,

Joe



Post #1505653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse