CODEWITHSUNDEEP
sqlsql-server-2005
Tommy Jakobsen
Tommy Jakobsen

Reputation: 2361

Query to filter table where the condition is given as a comma separated string

I need some help creating a query. Heres a sample table:

Segment   Name
1         Vincent
1         Jules
1         Lance
2         Vincent
3         Marsellus
4         Vincent
5         Marsellus

The problem is filtering this table. The column to filter on is the Segment column, and what to filter for is given as a string parameter, for example "1,2", meaning that I want Names that are in both Segment 1 and 2, returning the set:

Segment   Name
1         Vincent
2         Vincent

How can this be done? Help will be much appreciated. Thanks!

Upvotes: 1

Views: 1219

Answers (2)

Dave
Dave

Reputation: 1234

I solved this one by creating a function that parses a string into a table. Specifically, I have an array of items in a web form and instead of loading these items individually from the form, I opted to accept all items as a single comma separated string.

/*********************************************************
** Parse A Comma Delimited String Into A Table
** Description: When A Web Page Sends An Array Of Data To
** The Server, The Array Is Comma Delimited.
** This Routine Returns All Of The Data From A Comma
** Delimited String Into A Table.
*********************************************************/
CREATE FUNCTION dbo.ParseByComma ( @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
 VarSubString VARCHAR(10)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(10)
    -- Remove All Spaces
    SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
    RETURN
END

Upvotes: 0

gbn
gbn

Reputation: 432271

When you split the string you gets rows with values 1 and 2. Then, you take the rows where the count of matches = the number of rows.

DECLARE @string varchar(8000)
SET @string = '1,2'

DECLARE @Mytable TABLE (Segment int, name varchar(8000))
INSERT @Mytable VALUES (1, 'Vincent')
INSERT @Mytable VALUES (1, 'Jules')
INSERT @Mytable VALUES (1, 'Lance')
INSERT @Mytable VALUES (2, 'Vincent')
INSERT @Mytable VALUES (3, 'Marsellus')
INSERT @Mytable VALUES (4, 'Vincent')
INSERT @Mytable VALUES (5, 'Marsellus')

;WITH Split AS
(
    SELECT SplitValue, COUNT(*) OVER() AS NumSplitValues
    FROM dbo.FN_ListToTable(',', @string)
)
SELECT
    [Name]
FROM
    @Mytable T
    JOIN
    SPLIT S ON T.Segment = S.SplitValue
GROUP BY
    [Name], S.NumSplitValues
HAVING
    COUNT(*) = S.NumSplitValues

If you need Segment, then join back thus

;WITH Split AS
(
    SELECT SplitValue, COUNT(*) OVER() AS NumSplitValues
    FROM dbo.FN_ListToTable(',', @string)
), Matches AS
(
    SELECT
        [Name]
    FROM
        @Mytable T
        JOIN
        SPLIT S ON T.Segment = S.SplitValue
    GROUP BY
        [Name], S.NumSplitValues
    HAVING
        COUNT(*) = S.NumSplitValues
)
SELECT
    T.*
FROM
    Split S
    JOIN
    @Mytable T ON T.Segment = S.SplitValue
    JOIN
    Matches M ON T.[Name] = M.[Name]

The split functions relies on a numbers table and I grabbed it from google

CREATE FUNCTION dbo.FN_ListToTable (
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        SplitValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS SplitValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Number n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE SplitValue IS NOT NULL AND SplitValue <> ''
);
GO

Upvotes: 1

Related Questions