Show solving arithmetic expression step by step
I had a script to randomly generate arithmetic expressions for my 9-year old daughter's exercise (only +-*/ involved). Now she is asking for the step-by-step answers, mainly for showing how to handle the parenthesis, like the following:
2-(2-(-3)-(-2+5*(4-2)-2)*2-(4-2)*2+1)-(5*4-(6/3-1)+77/11)+22
=2-(2+3-(-2+5*2-2)*2-2*2+1)-(5*4-1+77/11)+22
=2-(2+3-6*2-2*2+1)-26+22
=2+10-26+22
=8
I create a script for this using a recursion procedure, and assuming that the input are always valid expressions. The script includes a helper function which is always in my toolbox (to split a string into list table), and a procedure to return the answer as the output parameter. Example is alos provided at the end of the script. Hope you enjoy.
Create function [dbo].[sp_split] (@ListString nvarchar(max), @Delimiter nvarchar(10), @IncludeEmpty int)
Returns @ListTable TABLE (ID int, ListValue nvarchar(max))
AS
BEGIN
Declare @CurrentPosition int, @NextPosition int, @Item nvarchar(max), @ID int, @L int
Select @ID = 1,
@L = len(replace(@Delimiter,' ','^')),
@ListString = @ListString + @Delimiter,
@CurrentPosition = 1
Select @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
While @NextPosition > 0 Begin
Set @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition)))
If @IncludeEmpty=1 or LEN(@Item)>0 Begin
Insert Into @ListTable (ID, ListValue) Values (@ID, @Item)
Set @ID = @ID+1
End
Set @CurrentPosition = @NextPosition+@L
Set @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
End
RETURN
END
go
Create procedure ShowSteps (
@ex varchar(1000),
@isDecimal int,
@result varchar(2000) OUTPUT )
as begin
set nocount on
declare @ex1 varchar(1000), @sql varchar(max), @r varchar(2000)
if @isDecimal = 1 select@ex = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@ex, '-','-1.00~'), '+','+1.00~'), '*','*1.00~'),'/','/1.00/'), '~','*')
select@ex = REPLACE(REPLACE(REPLACE(@ex,' ',''), '(','#('),')',')#')
select @result = ISNULL(@result, ''), @sql ='', @r=''
create table #t (id int, s varchar(1000))
insert into #t select * from dbo.sp_split(@ex,'#',0)
select @sql=@sql+';update #t set s = convert(varchar(1000), '+s+') where ID ='+convert(varchar, id) from #t where s like '(%)'
if len(@sql)<1 begin
select @sql = ';update #t set s = convert(varchar(1000), '+s+') where ID = 1' from #t
end
exec (@sql)
select @ex = ''
select @ex = @ex+ convert(varchar(1000), s) from #t order by ID
select @ex = REPLACE(@ex, '--','+')
drop table #t
select @result = @result+'='+ @ex+CHAR(13)+CHAR(10)
if CHARINDEX('-',REPLACE(REPLACE(REPLACE(@ex, '+','-'),'*','-'),'/','-'),2)>0 Begin
exec ShowSteps @ex, @isDecimal,@r OUTPUT
end
select @result = @result +@r
end
go
declare @ex varchar(1000), @r nvarchar(max)
select @ex = '2-(2-(-3)-(-2+5*(4-2)-2)*2-(4-2)*2+1)-(5*4-(6/3-1)+77/10)+22'
exec ShowSteps @ex,0,@r output
select @ex+char(13)+char(10)+@r 
