column and input withCSV

  • Hi

    I have TABLEA with a CSV column.

    TABLEA[colId,colcsv]

    TableA

    ----------

    1, A,B,C,D

    2, B,C

    3, D,E,F

    4, A,X,Y,Z,D

    I have to retrieve ID 1,2 if the input is "B,C"

    OR retrieve ID = 1,4 if the imput is "A,D".

    Any ideas please?

    Thanks.

  • Is this for an on-going processes? Is there always two search criteria? Or does this vary?



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Here's an idea. Notice that I'm including DDL and sample data as you should have done to get faster and better answers.

    I'm using a function that you can find in this article http://qa.sqlservercentral.com/articles/Tally+Table/72993/. Be sure to understand what it's doing.

    CREATE TABLE #TABLEA(colId int,colcsv varchar(8000))

    INSERT #TABLEA

    SELECT

    1, 'A,B,C,D' UNION ALL SELECT

    2, 'B,C' UNION ALL SELECT

    3, 'D,E,F' UNION ALL SELECT

    4, 'A,X,Y,Z,D'

    DECLARE @Value varchar(8000) = 'A,B';

    SELECT a.colId

    FROM #TABLEA a

    CROSS APPLY dbo.DelimitedSplit8K( colcsv, ',') split

    JOIN dbo.DelimitedSplit8K( @Value, ',') split2 ON split.Item = split2.Item

    GROUP BY colId

    HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.DelimitedSplit8K( @Value, ','))

    DROP TABLE #TABLEA

    Final note, try to normalize your data to avoid having this problems.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • one CSV input has to be matched with CSV column for individual items

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply