SQL替換欄位字串

1. ----------------------------
2. --Author:旋風
3. --Date:2008-10-23 15:19:49
4. --Version:V1.0
5. --Memo:SQL替換欄位字串
6. ----------------------------
7. --> Test Data: [A]
8. if object_id('[A]') is not null drop table [A]
9. create table [A] ([AutoID] int,[IDStr] varchar(50))
10. insert into [A]
11. select 1,'1,2,3,4' union all
12. select 2,'1,2'
13. --> Test Data: [B]
14. if object_id('[B]') is not null drop table [B]
15. create table [B] ([AutoID] int,[Value] varchar(3))
16. insert into [B]
17. select 1,'AAA' union all
18. select 2,'BBB' union all
19. select 3,'CCC' union all
20. select 4,'DDD'
22. Go
23. if object_id('S_str') is not null
24.     drop function S_str
25. go
26. create function S_str(@str nvarchar(100))
    returns nvarchar(100)
    as
    begin
        set @str=','+@str+','--加上兩邊逗號
        select @str=replace(@str,','+rtrim([AutoID])+',',','+[Value]+',') from B
        --where  @str like '%,'+rtrim([AutoID])+',%'
        WHERE CHARINDEX(','+RTRIM([AutoID])+',',@str)>0
        return substring(@str,2,len(@str)-2)--去掉兩邊逗號
    end
    go
    --Code
    --SQL2000用函數
    select [AutoID],[Value]=dbo.S_str([IDStr]) from A
29. --SQL2005用XML
30. SELECT [AutoID],
31.        [Value]=CAST(STUFF((
32.               SELECT ','+[Value]
33.               FROM B 
34.               WHERE CHARINDEX(','+RTRIM([AutoID])+',',','+a.[IDStr]+',')>0
35.               FOR XML PATH(''))
36.               ,1,1,'') AS VARCHAR(20)) 
37. FROM A AS a
38. --Drop
39. drop table [A]
40. drop function S_str
41. --Result
42. /*
43. AutoID      Value
44. ----------- ----------------------------------------------------------------------------------------------------
45. 1           AAA,BBB,CCC,DDD
46. 2           AAA,BBB
47. */