SQL Server 2008 R2：更新与另一个表匹配的表值

Question

Table: l_test1

CREATE TABLE l_test1
(
    Cola VARCHAR(10)
);

Table: l_test2

CREATE TABLE l_test2
(
    Cola VARCHAR(20)
);

插入:

INSERT INTO l_test1 VALUES('1');
INSERT INTO l_test1 VALUES('12');
INSERT INTO l_test1 VALUES('123');
INSERT INTO l_test1 VALUES('1234');

INSERT INTO l_test2 VALUES('991234567890');
INSERT INTO l_test2 VALUES('9912345678901');
INSERT INTO l_test2 VALUES('99123456789012');
INSERT INTO l_test2 VALUES('123991234567890');
INSERT INTO l_test2 VALUES('981234567890');
INSERT INTO l_test2 VALUES('1234991234567890');
INSERT INTO l_test2 VALUES('1981234567890');

注意: 现在我想要删除表格l_test2的起始和结束数字，这些数字与表格l_test1中的数字相匹配。

例如: 在上面的表格中，表格l_test1中有1,12,123,1234这些值。现在我想要删除表格l_test2中与这些数字匹配的值。表格l_test2中的第二条记录与表格l_test1中的值1匹配，因此应该将其删除。

更新所有值后，表格l_test2应如下所示:

期望结果:

Cola
---------------------------
991234567890
991234567890
991234567890
991234567890
981234567890
991234567890
981234567890

- MAK

Answer 1

使用 STUFF 函数：

查看示例

WITH cte1 AS
(
    SELECT t2.Cola, MAX(t1.Cola) AS r
    FROM #l_test2 t2
    JOIN #l_test1 t1
      ON t2.Cola LIKE t1.Cola + '%'
    GROUP BY t2.Cola
),
cte2 AS
(
    SELECT t2.Cola, MAX(t1.Cola) AS r
    FROM #l_test2 t2
    JOIN #l_test1 t1
     ON t2.Cola LIKE '%' + t1.Cola 
    GROUP BY t2.Cola
), cte3 AS
(
    SELECT Cola, STUFF(Cola, 1, LEN(r), '') AS sanitized
    FROM cte1
    UNION ALL
    SELECT Cola, STUFF(Cola, LEN(Cola) - LEN(r) + 1, LEN(r), '') AS sanitized
    FROM cte2
)
SELECT sanitized
FROM cte3
UNION ALL
SELECT Cola
FROM #l_test2 t
WHERE NOT EXISTS (SELECT 1 FROM cte3 c3 WHERE c3.Cola = t.Cola); 

我将此拆分为易读性更好的几个部分：

1. cte1 - 移除前缀
2. cte2 - 移除后缀
3. cte3 - 合并已清理的内容
4. final - 获取未被清理的行

请随意将我的解决方案合并为更简洁的形式；)

- Lukasz Szozda

---

非常感谢您。像往常一样，您非常棒 :) - MAK

第4行和第5行的结果与预期不符。数据以1和2结尾，而不是0。 - Iztoksson

@Uporabnik003，哦！是的！刚才我才知道。 - MAK

@MAK，我更新了我的解决方案。 - Lukasz Szozda

@Uporabnik003，感谢您找到了这个问题。 - MAK

Answer 2

我无法想到一个简单的方法来完成这个任务。这里提供一种尝试删除所有组合并寻找最短结果值的方法：

select substring(cola,
                 len(t1b.cola),
                 len(cola) - ( len(t1b.cola) + len(t1e.cola) )
                ) as new_cola
from (select t2.*,
             row_number() over (partition by seqnum order by len(t1b.cola) + len(t1e.cola) desc) as seqnum
      from (select t2.*, row_number() over (order by (select null)) as seqnum
            from l_test2 t2
           ) t2 left join
           l_test1 t1b
           on t2.cola like t1b.cola + '%' left join
           l_test1 t1e
           on t2.cola like '%' + t1e.cola
     ) t
where seqnum = 1;

- Gordon Linoff

Answer 3

您可以使用以下查询，其中使用了 PATINDEX 函数：

SELECT DISTINCT 
       Cola, 
       SUBSTRING(Cola, left_index + 1, right_index - left_index - 1) AS sanitized_Cola
FROM (
   SELECT MAX(CASE WHEN t3.left_match = 1 THEN LEN(t1.Cola) ELSE 0 END) 
          OVER (PARTITION BY t2.Cola) AS left_index,
          MIN(CASE WHEN right_match = 0 THEN LEN(t2.Cola)+1 ELSE right_match END) 
          OVER (PARTITION BY t2.Cola) AS right_index,                       
          t2.Cola
   FROM l_test2 AS t2
   CROSS JOIN l_test1 AS t1
   CROSS APPLY (SELECT PATINDEX(t1.Cola + '%', t2.Cola) AS left_match) AS t3 
   CROSS APPLY (SELECT PATINDEX('%' + t1.Cola, t2.Cola) AS right_match) AS t4 ) AS q 

这个想法是找到最大的匹配模式（如果有的话）向左和向右。然后使用SUBSTRING中这些匹配的索引来获取经过消毒的字符串。

这里有演示

- Giorgos Betsos

Answer 4

;WITH CTE AS 
(
SELECT L2.Cola, max(L1.Cola) AS substr ,'pre' as Flag
    FROM l_test1 L1 INNER JOIN l_test2 L2 ON L2.Cola LIKE L1.Cola+'%'
group by L2.Cola
union all 
SELECT L2.Cola, max(L1.Cola) AS substr ,'post' as Flag
    FROM l_test1 L1 INNER JOIN l_test2 L2 ON L2.Cola LIKE '%'+L1.Cola
group by L2.Cola
)
update l2   
    set Cola = SUBSTRING(cte.Cola,case Flag when 'pre' then len(substr) + 1 else 1 end,
                                len(cte.cola) - len(substr))
    from l_test2 l2 inner join cte on l2.Cola = cte.Cola;

使用一个CTE获取最大匹配字符串以及该匹配字符串的长度。

- OSAMA ORABI