sql拆分字符串的有关问题

sql拆分字符串的问题
有一个字符串'A,B,C,A,C,D',和一个字符串'1,2,5,4,3,1'

如果输入A 返回的值是5（说明:这个值是字符串对应的值 1+4）

请问怎么实现？实在搞不定，请各位大侠赐教

[解决办法]

SQL code

--字符串拆分create table #tab(    val nvarchar(50))declare @a nvarchar(1000)declare @len intselect @a='A,B,C,A,C,D'select @len=charindex(',',@a)  while(@len>0)begin    insert into #tab(val) values(substring(@a,1,@len-1))        select @a=substring(@a,@len+1,len(@a)-@len)        select @len=charindex(',',@a)endinsert into #tab(val) values(@a)select * from #tabdrop table #tab
[解决办法]
SQL code
/*按照符号分割字符串*/create function [dbo].[m_split2](@c varchar(2000),@split varchar(2))     returns @t table(col varchar(200))     as       begin         while(charindex(@split,@c)<>0)           begin               if(substring(@c,1,charindex(@split,@c)-1)!=' ')            begin          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))               end             set @c = stuff(@c,1,charindex(@split,@c),'')           end           if(@c!=' ' and @c is not null and @c!='')        begin      insert @t(col) values (@c)       end        return   enddeclare @d1 varchar(10)set @d1='A,B,C,A,C,D'declare @d2 varchar(10)set @d2='1,2,5,4,3,1'select a.col,sum(b.id) as id from (select *,row_number() over (order by (select 1)) as id from [dbo].[m_split2](@d1,',')) aleft join (select *,row_number() over (order by (select 1)) as id from [dbo].[m_split2](@d2,',')) bon a.id=b.id group by a.col/*col         id----------- --------------------A           5B           2C           8*/