Home Forums Programming General string of characters in separate Rows RE: string of characters in separate Rows

  • Here is a simple solution which uses charindex for the matching and a combination of len and replace to count the expected number of matches, should be enough to get you passed this hurdle.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @Table1 TABLE(indexColumn VARCHAR(50));

    INSERT INTO @Table1(indexColumn) VALUES ('cltstyle, InvNbr, PoNbr');

    DECLARE @Table2 TABLE (indexid INT NOT NULL, ColumnName VARCHAR(50) NOT NULL)

    INSERT INTO @Table2 (indexid,ColumnName)

    VALUES

    (1,'cltstyle')

    ,(2,'id')

    ,(3,'Tab9')

    ,(4,'cltstyle')

    ,(4,'InvNbr')

    ,(4,'PoNbr')

    ,(5,'InvNbr')

    ,(6,'ID')

    ,(6,'PoNbr')

    ,(6,'InvNbr');

    /*

    Results:

    4, cltstyle

    4, InvNbr

    4, PoNbr

    */

    ;WITH BASE_DATA AS

    (

    SELECT

    T2.indexid

    ,T2.ColumnName

    ,LEN(T1.indexColumn + CHAR(124)) - LEN(REPLACE(T1.indexColumn + CHAR(124),CHAR(44),'')) + 1 AS COL_COUNT

    ,COUNT(T1.indexColumn) OVER

    (

    PARTITION BY T2.indexid

    ) AS MATCH_COUNT

    FROM @Table2 T2

    OUTER APPLY @Table1 T1

    WHERE CHARINDEX(T2.ColumnName,T1.indexColumn) > 0

    )

    SELECT

    BD.indexid

    ,BD.ColumnName

    FROM BASE_DATA BD

    WHERE BD.COL_COUNT = BD.MATCH_COUNT;

    Results

    indexid ColumnName

    ----------- -----------

    4 cltstyle

    4 InvNbr

    4 PoNbr