请教一个关于SQL Server截取字符串的问题？

Question

现有表A，B：
A表：
A_Id A_Name
1    a,b,c,dB表：
B_Id B_Name
1    a
2    a,b
3    a,b,c,d
4    b,d
5    b,c,d
6    a,d
7    a,b,c现在想要的结果是B表中每一条记录的B_Name列相对于A表的A_Name的非B_NAme子集
结果：
B_Id B_Name
 1    b,c,d
 2    c,d
 3    null
 4    a,c
 5    a
 6    b,c
 7    d
即最终查询结果中B_Name字段的每一条记录都是B表B_Name字段在A_Name字段中未出现的字符串

Answer 1

select b.id,case when a.a_id is null then b.b_name end as b_name
from b
left join a on a.a_name=b.b_name

Answer 2

select id,replace(a.aname,b.bname,'')
from a cross join b截取都，前面还有一个逗号，自己再处理一下吧

Answer 3

B表B_Name列找逗号切割，在替换A表里的A_Name

Answer 4

没有做优化处理，可能写的比较繁琐
而且2000 不能用，只适用于2000以上的版本--> 测试数据：#A
IF OBJECT_ID('TEMPDB.DBO.#A') IS NOT NULL DROP TABLE #A
GO 
CREATE TABLE #A([A_Id] INT,[A_Name] VARCHAR(7))
INSERT #A
SELECT 1,'a,b,c,d'
--> 测试数据：#B
IF OBJECT_ID('TEMPDB.DBO.#B') IS NOT NULL DROP TABLE #B
GO 
CREATE TABLE #B([B_Id] INT,[B_Name] VARCHAR(7))
INSERT #B
SELECT 1,'a' UNION ALL
SELECT 2,'a,b' UNION ALL
SELECT 3,'a,b,c,d' UNION ALL
SELECT 4,'b,d' UNION ALL
SELECT 5,'b,c,d' UNION ALL
SELECT 6,'a,d' UNION ALL
SELECT 7,'a,b,c'
--------------开始查询--------------------------; WITH cte AS(
SELECT  T.c.value('.' , 'varchar(10)') AS nameA
FROM    (
         SELECT CAST( '<x>'+ REPLACE ([A_Name] , ',' , '</x><x>')+ '</x>' AS XML) AS name FROM #A
        ) A
CROSS APPLY A.name.nodes('/x/text()') T (c)
 )
,cte2 AS(
SELECT  [B_Id] , T.c.value('.' , 'varchar(10)') AS nameB , row_id = ROW_NUMBER() OVER (PARTITION BY [B_Id] ORDER BY T.c.value('.' , 'varchar(10)'))
FROM    (
         SELECT [B_Id] , CAST( '<x>'+ REPLACE ([B_Name] , ',' , '</x><x>')+ '</x>' AS XML) AS name FROM #B
        ) B
CROSS APPLY B.name.nodes('/x/text()') T (c)
)
,cte3 AS 
( 
SELECT  DISTINCT
        [B_Id] , nameA
FROM    cte2 AS t
OUTER  APPLY (
              SELECT * FROM cte WHERE nameA NOT IN( SELECT nameB FROM cte2 WHERE [B_Id]= t.[B_Id])
             ) app)
SELECT [B_Id],[B_Name]=STUFF((SELECT ','+nameA FROM cte3 WHERE [B_Id]=t.[B_Id] ORDER BY nameA FOR XML PATH('') ),1,1,'') FROM cte3 AS t
GROUP BY [B_Id]
ORDER BY [B_Id]
 
    
----------------结果----------------------------
/* 
B_Id B_Name
1 b,c,d
2 c,d
3 NULL
4 a,c
5 a
6 b,c
7 d
*/

Answer 5

--> 测试数据：#A
IF OBJECT_ID('TEMPDB.DBO.#A') IS NOT NULL DROP TABLE #A
GO 
CREATE TABLE #A([A_Id] INT,[A_Name] VARCHAR(7))
INSERT #A
SELECT 1,'a,b,c,d'
--> 测试数据：#B
IF OBJECT_ID('TEMPDB.DBO.#B') IS NOT NULL DROP TABLE #B
GO 
CREATE TABLE #B([B_Id] INT,[B_Name] VARCHAR(7))
INSERT #B
SELECT 1,'a' UNION ALL
SELECT 2,'a,b' UNION ALL
SELECT 3,'a,b,c,d' UNION ALL
SELECT 4,'b,d' UNION ALL
SELECT 5,'b,c,d' UNION ALL
SELECT 6,'a,d' UNION ALL
SELECT 7,'a,b,c'
 
---写个2000的，用函数分割替换 
 create function f_name(@name varchar(20),@rename varchar(20))
 returns varchar(20)
 begin
      select @name=@name+',',@rename=','+@rename
      while charindex(',',@name)>0
      begin
          set @rename=replace(@rename,','+left(@name,charindex(',',@name)-1),'')
          set @name=right(@name,len(@name)-charindex(',',@name))  
      end
      return stuff(@rename,1,1,'')
 end
 
 select n.B_ID,
        B_name=dbo.f_name(n.B_name,m.A_name)
 from #A m cross join #B n
 
 /*
 B_ID        B_name
----------- --------------------
1           b,c,d
2           c,d
3           NULL
4           a,c
5           a
6           b,c
7           d(7 row(s) affected)