SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filter recordset with comma delimited search criteria


Filter recordset with comma delimited search criteria

Author
Message
joe-584802
joe-584802
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 96
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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3896 Visits: 8472
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
;




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
joe-584802
joe-584802
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 96
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search