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