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

请教一个关于SQL Server截取字符串的问题？
现有表A，B：
A表：
A_Id A_Name
1 a,b,c,d

B表：
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字段中未出现的字符串


[解决办法]

SQL code

select b.id,case when a.a_id is null then b.b_name end as b_namefrom bleft join a on a.a_name=b.b_name
[解决办法]
select id,replace(a.aname,b.bname,'')
from a cross join b

截取都，前面还有一个逗号，自己再处理一下吧
[解决办法]
B表B_Name列找逗号切割，在替换A表里的A_Name
[解决办法]
没有做优化处理，可能写的比较繁琐
而且2000 不能用，只适用于2000以上的版本

SQL code
--> 测试数据：#AIF OBJECT_ID('TEMPDB.DBO.#A') IS NOT NULL DROP TABLE #AGO CREATE TABLE #A([A_Id] INT,[A_Name] VARCHAR(7))INSERT #ASELECT 1,'a,b,c,d'--> 测试数据：#BIF OBJECT_ID('TEMPDB.DBO.#B') IS NOT NULL DROP TABLE #BGO CREATE TABLE #B([B_Id] INT,[B_Name] VARCHAR(7))INSERT #BSELECT 1,'a' UNION ALLSELECT 2,'a,b' UNION ALLSELECT 3,'a,b,c,d' UNION ALLSELECT 4,'b,d' UNION ALLSELECT 5,'b,c,d' UNION ALLSELECT 6,'a,d' UNION ALLSELECT 7,'a,b,c'--------------开始查询--------------------------; WITH cte AS(SELECT  T.c.value('.' , 'varchar(10)') AS nameAFROM    (         SELECT CAST( '<x>'+ REPLACE ([A_Name] , ',' , '</x><x>')+ '</x>' AS XML) AS name FROM #A        ) ACROSS 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        ) BCROSS APPLY B.name.nodes('/x/text()') T (c)),cte3 AS ( SELECT  DISTINCT        [B_Id] , nameAFROM    cte2 AS tOUTER  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 tGROUP BY [B_Id]ORDER BY [B_Id]     ----------------结果----------------------------/* B_Id    B_Name1    b,c,d2    c,d3    NULL4    a,c5    a6    b,c7    d*/
[解决办法]
SQL code
--> 测试数据：#AIF OBJECT_ID('TEMPDB.DBO.#A') IS NOT NULL DROP TABLE #AGO CREATE TABLE #A([A_Id] INT,[A_Name] VARCHAR(7))INSERT #ASELECT 1,'a,b,c,d'--> 测试数据：#BIF OBJECT_ID('TEMPDB.DBO.#B') IS NOT NULL DROP TABLE #BGO CREATE TABLE #B([B_Id] INT,[B_Name] VARCHAR(7))INSERT #BSELECT 1,'a' UNION ALLSELECT 2,'a,b' UNION ALLSELECT 3,'a,b,c,d' UNION ALLSELECT 4,'b,d' UNION ALLSELECT 5,'b,c,d' UNION ALLSELECT 6,'a,d' UNION ALLSELECT 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,d2           c,d3           NULL4           a,c5           a6           b,c7           d(7 row(s) affected)