1.0(3),2.0(4),3.5(5),3.5111(78),55.98(23345) 12.0(2),3.5(5) 4567.0(344),234.43(2323),3.5(5),3.5111(78),55.98(23345) 1307155.46580 41.50000 3422773.35580 create function toolkindvalues(@str varchar(8000) ) returns decimal(18,5) as begin declare @result decimal(18,5) set @result=0 declare @charindex int set @charindex=0 declare @replacestr varchar(500) while (charindex(',',@str,0)>0) begin select @charindex=charindex(',',@str,0) select @replacestr=substring (@str,0,@charindex) select @str= replace( @str,@replacestr+',','') --select @replacestr --select left(@replacestr, charindex( '(',@replacestr,0)-1),left( right(@replacestr,2),len(right(@replacestr,2))-1) select @result=@result+ cast( left(@replacestr, charindex( '(',@replacestr,0)-1) as decimal(18,5)) * cast ( substring( replace( @replacestr,left(@replacestr, charindex( '(',@replacestr,0)-1),''),2,len(replace( @replacestr,left(@replacestr, charindex( '(',@replacestr,0)-1),''))-2) as decimal(18,5) ) end --select @str select @result=@result+ cast( left(@str, charindex( '(',@str,0)-1) as decimal(18,5)) * cast ( substring( replace( @str,left(@str, charindex( '(',@str,0)-1),''),2,len(replace( @str,left(@str, charindex( '(',@str,0)-1),''))-2) as decimal(18,5) ) return @result end --drop table #ddd create table #ddd(str varchar(500)) insert #ddd select '1.0(3),2.0(4),3.5(5),3.5111(78),55.98(23345)' union select '4567.0(344),234.43(2323),3.5(5),3.5111(78),55.98(23345)' union select '12.0(2),3.5(5)' select * from #ddd select dbo.toolkindvalues(str) from #ddd