在这里,我们将使用PARSE NAME使用ID值来创建它们,然后将它们与现有记录的两个表进行比较。
declare @table1 table (ID varchar(10))
insert into @table1(ID) values ('3,4,6'),('1,2,3'),('2,3'),('1,2,3'),('1,2')
declare @table2 table (ID int)
insert into @table2(ID) values(1),(2),(3),(4),(5),(6),(7),(8)
Declare @Custom Table (ID INT)
;WITH CTE AS (
SELECT
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'ID'
FROM
(
SELECT
CAST ('<M>' + REPLACE([ID], ',', '</M><M>') + '</M>' AS XML) AS Data
FROM @table1
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a))
INSERT INTO @Custom (ID)
Select T.ID from @table2 T
WHERE EXISTS
(SELECT DISTINCT C.ID
FROM CTE C
WHERE T.ID = C.ID)
select * from @Custom
;WITH CTE AS (
SELECT
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'ID'
FROM
(
SELECT
CAST ('<M>' + REPLACE(Id, ',', '</M><M>') + '</M>' AS XML) AS Data
FROM table1
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a))
SELECT ColumnName FROM CTE C
join table2 im on im.Id = c.ID