请教一个关于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字段中未出现的字符串

[百年树人]

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

[让你望见影子的墙]

select id,replace(a.aname,b.bname,'')
from a cross join b

截取都，前面还有一个逗号，自己再处理一下吧

[快溜]

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

[汤姆克鲁斯]

没有做优化处理，可能写的比较繁琐
而且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
*/

[快溜]

--> 测试数据：#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)

[马栏山的飘飘]

呵呵，感谢大家的帮助，我的问题已经解决了，谢谢大家，大家太热心了！