In the front end a user inputs how to configure the sell price of the part "AmtCost * 1.18". This is contained in a text field. How do I extract that out of the text to use in a select statement?
AmtCost is a field name. The user could use AmtPrice1(another Field name) or AmtCost2 or about 5 other cost fields.
So the users specifies which field he wants to use in the calculation then the (* or / or +) and then specifies the factor such as 1.18.
Not sure how to explain, I hope this make sense.
To do this you will need dynamic SQL - and since that is a 'text' field it is going to open you up to possible SQL injection attacks.
A better model might be a column that contains the 'field' to be used, a separate column for the operator - and a separate column for the factor. This would still require dynamic SQL - but you then can more control over what values can be entered by the user.
Either way - you will need dynamic SQL where you build of the SQL statement and then execute that statement.
Personally I'd add a trigger to validate and pre-parse the expression as it's inserted or updated in the table. The code that does the actual calc should not have to worry about validation.