October 16, 2013 at 1:50 am
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.
[font="Courier New"]
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
[/font]
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
October 16, 2013 at 2:36 am
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/%5B/url%5D) : -
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
;
October 17, 2013 at 6:14 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply