Post reply

column and input withCSV

  • January 15, 2014 at 11:21 am

    #396113

    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.

  • January 15, 2014 at 11:33 am

    #1680385

    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]

  • January 15, 2014 at 11:39 am

    #1680387

    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

  • January 15, 2014 at 11:47 am

    #1680391

    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