--创建表值函数
if OBJECT_ID('[dbo].[SplitStr]') is not null drop function [dbo].[SplitStr]
go
create 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_value
294
*/

------解决方案--------------------
SQL code

declare  @s varchar(100) = '39*516,40*1971,41*2675,42*2520,43*1609,44*616,45*196',
        @result int = 0
set @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 @s
end
select @result