How do I select the following string so it returns 3? SELECT '1+2'

Question

In a column "SumStrings" of a table I have strings such as '1+2' or '1+2-3' like this:

SumStrings
1+2
1+2-3

In other words:

DROP TABLE IF EXISTS #theSums;
CREATE TABLE #theSums (SumStrings VARCHAR(25))
INSERT INTO #theSums
    values
        ('1+2'),
        ('1+2-3');

How do I select from this table to give me the results of these sums? i.e. if only the above two strings were in the table then the result of the selection should be 3 and 0

This question String Expression to be evaluated to number is mainly directed to the creation of a function I would just like a simple script that selects from the table

Answer 1

Here is one way using dynamic sql

DECLARE @sql VARCHAR(8000) 

SELECT @sql = Stuff((SELECT 'union all select ' + sumstrings 
                     FROM   #thesums 
                     FOR xml path('')), 1, 9, '') 

PRINT @sql 

EXEC (@sql) 

Answer 2

No easy going here... You might use dynamically created statements:

Prdp's answer is great, just an addition against SQL-injection

You can use XML's implicit capability to compute a - that's the backdraw! - literal value. No way around dynamic SQL, but this would help against insane values:

Try this

SELECT CAST('' AS XML).value('1+2','int') AS Result;

This example with a CURSOR, but the same can be done with @Prdp's approach:

CREATE TABLE YourTable(ComputeString VARCHAR(100));
INSERT INTO YourTable VALUES('1+2'),('-2+3'),('3*(4+5)'),('12/4');

DECLARE @cs VARCHAR(100);

DECLARE c CURSOR FOR SELECT 'SELECT CAST('''' AS XML).value(''' +  REPLACE(ComputeString,'/',' div ') + ''',''int'') AS Result;' FROM YourTable
OPEN c;
FETCH NEXT FROM c INTO @cs;
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @cs
    EXEC(@cs);
    FETCH NEXT FROM c INTO @cs;
END
CLOSE c;
DEALLOCATE c;
GO
DROP TABLE YourTable;

Remark

You'd have to replace a / as divisor operator with the word div