Results:
select fooID from dbo.IntersectFooBoxes('FOOBIE BLETCH')
--RESULT: 0,5,6,7
select fooID from dbo.IntersectFooBoxes('GARVEN DEH, DAIYEN FOOELS, ELBIB YLOH')
--RESULT: 3,8
select fooID from dbo.IntersectFooBoxes('FNORD, JUYED AWK YACC')
--RESULT: 4
Setup:
CREATE TABLE foo
(
ID int NOT NULL PRIMARY KEY,
Box1 varchar(50),
Box2 varchar(50),
Box3 varchar(50)
)
GO
INSERT INTO foo (ID, Box1, Box2, Box3)
SELECT 0, 'FOOBIE BLETCH', NULL, NULL UNION ALL
SELECT 1, 'DUAM XNAHT', NULL, NULL UNION ALL
SELECT 2, 'HACKEM MUCHE', 'FNORD', NULL UNION ALL
SELECT 3, 'DAIYEN FOOELS', 'ELBIB YLOH', 'GARVEN DEH' UNION ALL
SELECT 4, 'JUYED AWK YACC', 'FNORD', NULL UNION ALL
SELECT 5, 'FOOBIE BLETCH', NULL, NULL UNION ALL
SELECT 6, 'JUYED AWK YACC', 'FOOBIE BLETCH', NULL UNION ALL
SELECT 7, 'HACKEM MUCHE', 'FNORD', 'FOOBIE BLETCH' UNION ALL
SELECT 8, 'DAIYEN FOOELS', 'GARVEN DEH', 'ELBIB YLOH'
GO
Function: LookupFooBoxes
This function takes a single value and returns any fooIDs that contain this value in any of the desired columns. Use this function to have a single place to define which columns to search.
CREATE FUNCTION dbo.LookupFooBoxes (@Value varchar(50))
RETURNS @results TABLE
(
-- Columns returned by the function
ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
fooID INT
)
AS
BEGIN
INSERT INTO @results (fooID)
SELECT ID
FROM
(
SELECT ID FROM foo WHERE Box1 = @Value
UNION
SELECT ID FROM foo WHERE Box2 = @Value
UNION
SELECT ID FROM foo WHERE Box3 = @Value
) matches
ORDER BY ID asc
RETURN;
END
Function: Split
This function takes a @Values parameter with a separated 'list' of values, and a @Split parameter that defines what to split on (default: comma), and also trims any spaces out of the resulting values
CREATE FUNCTION dbo.Split (@Values varchar(max), @Split varchar(5) = ',')
RETURNS @result TABLE
(
-- Columns returned by the function
ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
Value varchar(max)
)
AS
BEGIN
DECLARE @X xml
SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@Values, @Split,'</s> <s>') + '</s> </root> ')
INSERT INTO @result (Value)
SELECT LTRIM(RTRIM(T.c.value('.','varchar(max)')))
FROM @X.nodes('/root/s') T(c)
RETURN;
END
Function: InsersectFooBoxes
This function takes a comma separated list of values as a single parameter, and iterates through them with cursor. If there are no values it returns an empty table, for the first value it just uses dbo.LookupFooBoxes to find all the rows that contain that value, and for any other values it removes IDs that do not contain it, resulting in the intersection of only those fooIDs that have all the elements.
CREATE FUNCTION dbo.IntersectFooBoxes (@Values varchar(max))
RETURNS @results TABLE
(
-- Columns returned by the function
ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
fooID INT
)
AS
BEGIN
DECLARE c CURSOR FOR SELECT Value FROM dbo.Split(@Values, ',')
OPEN c
DECLARE @value varchar(max)
FETCH NEXT FROM c INTO @value
--No Elements
IF @@FETCH_STATUS <> 0
RETURN;
INSERT INTO @results (fooID)
SELECT fooID FROM dbo.LookupFooBoxes(@Value)
FETCH NEXT FROM c INTO @value
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE @results
FROM
@results r
LEFT OUTER JOIN dbo.LookupFooBoxes(@Value) m ON m.fooID = r.fooID
WHERE
m.fooID is null
FETCH NEXT FROM c INTO @value
END
RETURN;
END
