字符串截取，然后在相加有关问题

字符串截取，然后在相加问题
现有一个字符串nvarchar类型

39*516,40*1971,41*2675,42*2520,43*1609,44*616,45*196

我要去除每个*号后面的 516 1971 2675 2520 1609 616 196  

然后把他们相加

请问，该如何做

[解决办法]

SQL code

--创建表值函数if OBJECT_ID('[dbo].[SplitStr]') is not null drop function [dbo].[SplitStr]gocreate function [dbo].[SplitStr](@splitString varchar(8000), @separate varchar(10))returns @returnTable table(col_Value varchar(20)) AS begin declare @thisSplitStr varchar(20) declare @thisSepIndex int declare @lastSepIndex int set @lastSepIndex =0 if Right(@splitString,len(@separate))<>@separate set @splitString=@splitString+@separate set @thisSepIndex=CharIndex(@separate,@splitString ,@lastSepIndex) while @lastSepIndex<=@thisSepIndex begin set @thisSplitStr = SubString(@splitString ,@lastSepIndex,@thisSepIndex-@lastSepIndex) set @lastSepIndex = @thisSepIndex + 1 set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex) insert into @returnTable values(@thisSplitStr) end return end go--将字符串拆分成列进行处理declare @str nvarchar(200)set @str='39*516,40*1971,41*2675,42*2520,43*1609,44*616,45*196'select SUM(CAST(LEFT(col_Value,CHARINDEX('*',col_Value,1)-1) AS int)) sum_value FROM SplitStr(@str,',')--查询结果/*sum_value294*/
[解决办法]
SQL code
declare  @s varchar(100) = '39*516,40*1971,41*2675,42*2520,43*1609,44*616,45*196',        @result int = 0set @s = @s + ','while (CHARINDEX('*',@s) > 0)begin    IF CHARINDEX(',',@s) = 1        set @s = SUBSTRING(@s,2,LEN(@s))    set @result = @result + CONVERT(int, SUBSTRING(@s,0,charindex('*',@s)))    set @s = SUBSTRING(@s,charindex(',',@s)+1,LEN(@s))    select @sendselect @result