Replace multiple characters based on table values

Question

Post reply

Replace multiple characters based on table values

sergiosck

SSC Rookie

Points: 46
More actions
December 12, 2014 at 4:16 am

#293402

Hello
It's my first post here but you guys are doing incredible job
I am trying to do something that seemed quite simple but eventually can't figure it out
There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]
[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]
SET @Total='select '+@formula
EXECUTE sp_executesql @Total;
PRINT @Total;
Can you think of any smarter solution that this?
Thank you

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

If the formula is addition only then using Jeff Moden's splitter

SELECT STUFF((SELECT '+' + CAST(t.Total as varchar(10))

FROM DelimitedSplit8K(@formula,'+') f

JOIN Totals t ON t.RowNo = f.Item

ORDER BY ItemNumber

FOR XML PATH ('')),1,1,'')

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65144 More actions · Answer 2

Solution for additional maths

SELECT (SELECT ISNULL(CAST(t.Total as varchar(10)),f.Item)

FROM master.dbo.DelimitedSplit8K(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@formula,'+','|+|'),'-','|-|'),'*','|*|'),'/','|/|'),'(','|(|'),')','|)|')

,'|') f

LEFT JOIN Totals t ON t.RowNo = f.Item

ORDER BY ItemNumber

FOR XML PATH (''))

Far away is close at hand in the images of elsewhere.
Anon.

sergiosck SSC Rookie Points: 46 More actions · Answer 3

David Burrows (12/12/2014)
Solution for additional maths
SELECT (SELECT ISNULL(CAST(t.Total as varchar(10)),f.Item)
FROM master.dbo.DelimitedSplit8K(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@formula,'+','|+|'),'-','|-|'),'*','|*|'),'/','|/|'),'(','|(|'),')','|)|')
,'|') f
LEFT JOIN Totals t ON t.RowNo = f.Item
ORDER BY ItemNumber
FOR XML PATH (''))

Thanks a lot David, works wonderfully even in more intricate calculations !