-1

Is anyone aware of any ability to solve an algebraic equation within SQL Server

One table contains questions and answers

Question Answer WIDTH 700 LENGTH 200

The other table contains the formulas (WIDTH + 100) / LENGTH

The returned value i'm looking for is 4

The questions are a dynamic list so each time it runs the questions would be slightly different, plus the list grows so i would have to be able to add to the list independently without it affecting the sql statement.

Gareth Bennett
  • 31
  • 1
  • 6
  • Must you only use SQL? – Soham Chowdhury Apr 21 '13 at 06:25
  • If you don't somehow tell SQL what `WIDTH` and `LENGTH` should be, how is it going to know what values to use? Your question is confusing... – Tim Apr 21 '13 at 06:25
  • What SQL are you using? PostgreSQL has the `EXECUTE` statement. Possible duplicate of: http://stackoverflow.com/questions/7433201/are-there-any-way-to-execute-a-query-inside-the-string-value-like-eval-in-post – Soham Chowdhury Apr 21 '13 at 06:27
  • Are you really asking for SQL to evaluate a string held in a table as an expression, so there's a column in the database that contains the string `(WIDTH + 100) / LENGTH` and somewhere (else) a column for WIDTH that contains 700 and a column for LENGTH that contains 200 and the expression should calculate `(700 + 100) / 200` based on the string expression. But if the next row in the table with the string expression contained `(LENGTH * WIDTH) / 100`, you'd expect an answer of 1400 for that row, and so on? – Jonathan Leffler Apr 21 '13 at 06:36
  • @Johnathan Leffler. Yes that is what i'm after. The questions change as well as the formulas. I'm reading the answer from Gabe but not following what hes doing with DIMENSIONS. DIMENSIONS in this instance would be a table with two columns QUESTION and ANSWER ie LENGTH, 200 – Gareth Bennett Apr 21 '13 at 07:19
  • Put succinctly, SQL is not the language to execute those expressions in. SELECT the data, but do the calculation in the client. – Jonathan Leffler Apr 21 '13 at 14:27

2 Answers2

1

SQL is perfectly capable of simple math. Here's an example:

SELECT (WIDTH + 100) / LENGTH
FROM DIMENSIONS

So if you have a table containing a mathematical expression as a string, you can query for the string, compose it into a new SQL query, and execute the new query. For example:

SELECT 'SELECT ' || Expression || ' FROM DIMENSIONS'
FROM Expressions

If Expressions is a table with a column Expression having a value (WIDTH + 100) / LENGTH, the result will be the SQL query in my first example. Then simply execute the returned SQL.

NOTE: If the expression comes from an untrusted source (e.g. a user on the Internet), directly executing their input is not safe to do.

Gabe
  • 84,912
  • 12
  • 139
  • 238
  • I think that is **very** different from what the question is asking. There's a column in the database that contains the string `(WIDTH + 100) / LENGTH` and somewhere (else) a column for WIDTH that contains 700 and a column for LENGTH that contains 200 and the expression should calculate `(700 + 100) / 200` based on the string expression. – Jonathan Leffler Apr 21 '13 at 06:34
  • @JonathanLeffler: Yes, I see what you mean. I've updated my answer accordingly. – Gabe Apr 21 '13 at 06:43
  • @Gabe DIMENSIONS in my situation is a table containing two columns QUESTION and ANSWER. So the rows would be, "LENGTH","200" and "WIDTH","700" – Gareth Bennett Apr 21 '13 at 07:26
1

I think you will have to build up a string and then execute it. e.g. in puedo SQL, something like;

    declare SQLSTR varchar2(2000)

    set SQLSTR = select 'select '''+REPLACE(
                                      REPLACE(<column name holding formal>), 'WIDTH', <column holding width>), 
                                                                             'LENGTH', <column name holding length>) '''
    + ' from <table holding width and length columns> '
    + ' where <predicate>'
from <table holding formula.
where <predicate>

    exec (SQLSTR)
Karl
  • 3,312
  • 21
  • 27
  • This could be made to work, but it isn't what SQL is designed for. (That is, given the problem as stated, this or something similar using dynamic SQL is likely to be the necessary solution — the problem should be re-stated and the evaluation of the expression done on the client side.) – Jonathan Leffler Apr 21 '13 at 14:55
  • I disagreed. This is the sort of thing that wokrs well with SQL. THere is no reason to use the RDBMS, move the data over the wire, use the client to do some of the processing, grab some more data over the wire, complete processing. Just use the fine, powerful, probably expensive RDBMS to its maximum. – Karl Apr 21 '13 at 19:13