带有子字符串charindex的sql查询

这不干净，也不是我推荐的东西，但您可能会找到一些帮助：

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (1,43,1,NULL)
,(2,46,1,'111,112,113')
,(3,43,2,NULL)
,(4,46,2,'90,5')

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (112,'cat1')
,(5,'cat2');

SELECT t1.id
      ,t1.id_data
      ,t1.id_t
      ,ID.List
      ,(
        SELECT t2.cat
        FROM @table2 AS t2 WHERE CHARINDEX(',' + CAST(t2.id_value AS VARCHAR(100)) + ',',',' + ID.List + ',')>0
       )
FROM @table1 AS t1
OUTER APPLY(SELECT x.value FROM @table1 AS x WHERE x.id_t=t1.id_t AND x.value IS NOT NULL) AS ID(List)
WHERE t1.value IS NULL

更新：您关于使用第二个数字作为更新的解释

试试这个

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (1,43,1,NULL)
,(2,46,1,'111,112,113')
,(3,43,2,NULL)
,(4,46,2,'90,5')

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (112,'cat1')
,(5,'cat2');

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.value IS NOT NULL) AS ID(SecondNr)
    WHERE t1.value IS NULL
)
UPDATE UpdateableCTE SET value=NewCat;

SELECT * FROM @table1

这个概念是可更新的 CTE，您可以在其中使用法线SELECT来获取所需的值。然后您可以直接更新派生表（只要只有一个表的列受到影响）。

在OUTER APPLY使用了一招XML到CSV列表以便读取第二个数字拆分。

更新 2：使用您的新样本数据

以下将使用新的示例数据并使用 ID：

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (17974492,1,999251,'somevalue')
,(17974493,2,999251,'somevalue')
,(17974494,3,999251,'somevalue')
,(17974495,4,999251,'somevalue')
,(17974496,5,999251,'somevalue')
,(17974497,43,999251,'(thishsouldbeupdated)')
,(17974498,6,999251,'somevalue')
,(17974499,7,999251,'somevalue')
,(17974500,46,999251,'111,311')
,(17974501,8,999251,'somevalue')
,(17974502,9,999251,'somevalue')
,(17974503,10,999251,'somevalue')
,(17974504,11,999251,'somevalue')
,(17974505,12,999251,'somevalue')
,(17974506,13,999251,'somevalue')
,(17974507,1,999252,'somevalue')
,(17974508,2,999252,'somevalue')
,(17974509,3,999252,'somevalue')
,(17974510,4,999252,'somevalue')
,(17974511,5,999252,'somevalue')
,(17974512,43,999252,'(thisshouldbeupdated)')
,(17974513,6,999252,'somevalue')
,(17974514,7,999252,'somevalue')
,(17974515,46,999252,'98,98')
,(17974516,8,999252,'somevalue')
,(17974517,9,999252,'somevalue')
,(17974518,10,999252,'somevalue')
,(17974519,11,999252,'somevalue')
,(17974520,12,999252,'somevalue')
,(17974521,13,999252,'somevalue');

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (311,'cat1')
,(98,'cat2');

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.id_data=46) AS ID(SecondNr)
    WHERE t1.id_data=43
)
UPDATE UpdateableCTE SET value=NewCat;

SELECT * FROM @table1;