如何将字符型字段中的“1*2*3”select得出相乘的结果6啊？

[wangjun2000]

如题，表中有字符型字段，内容为1*2*3，现在将字段内容作为表达式运算得出6，请问有什么办法啊？
分析字符串，拆分出1，2，3，然后运算，这种方式不算啊，要其他的高招。
祝大家元旦快乐啊！

[叶子]

--定义一个变量
declare @a varchar(10) 
set @a='1*2*3'

exec ('select '+@a)
/*
6
*/

[wangjun2000]

to maco_wang:
下面是我的代码
declare @a varchar(40)
declare @b varchar(30)
declare @c int
set @a='1*2*3'
set @b=' select '+ @a
if object_id('tempdb..#') is not null drop table #
create table #(c1 varchar(100))
insert # exec(@b)
select @c=c1 from #
select @c

此时c的值的确是1*2*3的结果6，但问题是我要将这段代码作为函数使用的话，sqlserver是不允许在函数内部操作临时表的，换成用户表的话，又会带来并发的问题。头疼。

[叶子]

/*计算字符串的值*/
create function [dbo].[m_charcompute](@bds varchar(1000))
returns float
as
BEGIN
set @bds = replace(@bds,' ','')--去空格，免得麻烦。
declare @i int,@j int 
declare @c1 char(1),@c2 char(1),@c varchar(100)
declare @v1 float,@v2 float,@v float
declare  @t table(id int identity(1,1),s varchar(100))
declare  @s table(id int identity(1,1),s varchar(100))
declare  @sv table(id int identity(1,1),v float)

select @i = 0,@j = len(@bds),@c2 = '',@c = ''
while @i<@j
begin
 select @c1 = @c2,@i = @i+1
 select @c2 = substring(@bds,@i,1)
if charindex(@c2,'.0123456789') > 0 or (@c2 = '-' and @c1 in('','*','-','+','/','(')) 

 begin   select @c = @c + @c2   continue  end
 if @c <> ''  begin insert @t(s)  select @c select @c = '' end
 if charindex(@c2,')')>0
 begin 
   insert @t(s)  select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc
   delete @s where id >= isnull((select max(id) from @s where s in('(')),0) 
   continue
 end
 if charindex(@c2,'+-)')>0
 begin 
   insert @t(s)  select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc
   delete @s where id > isnull((select max(id) from @s where s in('(')),0) 
   if @c2 <> ')' insert @s(s) select @c2
   continue
 end
 if charindex(@c2,'*/')>0
 begin 
   insert @t(s)  select s from @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) order by id desc
   delete @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) 
   insert  @s select @c2
   continue
 end
 if charindex(@c2,'(')>0 insert  @s select @c2
end
if @c <> '' insert @t(s) select @c
insert @t(s)  select s from @s order by id desc
select @i = 0,@j = max(id) from @t 
while @i < @j
begin 
 select @i = @i + 1
 select @c = s from @t where id = @i
 if @c = '(' continue
 if @c not in('*','-','+','/')  begin  insert @sv(v) select convert(float,@c) continue end
 select @v2 = v from @sv  delete @sv  where id = (select max(id) from @sv)
 select @v1 = v from @sv  delete @sv  where id = (select max(id) from @sv)
 select @v = case @c when '+' then @v1 + @v2 when '-' then @v1 - @v2
                     when '*' then @v1 * @v2 when '/' then @v1 / @v2 end
 insert @sv(v) select @v
end
select @v = v from @sv
return @v
end


--测试
select dbo.[m_charcompute] ('1*2*3')
/*
6
*/