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

There no build-in function to do this, you should write it yourself — Mikhail Lobanov, Jul 03 '17 at 15:24
Not sure why people downvote this question. Might not be the best way to store data still a interesting question imo — Pரதீப், Jul 03 '17 at 15:25
@TimSchmelter Maybe he's generalizing the problem hoping to find out that there's a built-in SQL Server function that exposes SQL Server's expression evaluation interface? — Jeff Holt, Jul 03 '17 at 15:26
Well, i'd make my life easier and use .NET's [`DataTable.Compute`](https://stackoverflow.com/a/18796518/284240) method, maybe there is something. Solving this in the database can get out of hand — Tim Schmelter, Jul 03 '17 at 15:26
@TimSchmelter its to solve a logic puzzle and we'd like to use only sql: puzzle is via some bloke at GCHQ — whytheq, Jul 03 '17 at 15:28
@Prdp thanks for the support - yes it wouldn't be ideal if the first idea that popped into a data warehouse designers mind! but still sort of interesting — whytheq, Jul 03 '17 at 15:30
Possible duplicate of [String Expression to be evaluated to number](https://stackoverflow.com/questions/9850894/string-expression-to-be-evaluated-to-number) — JeffUK, Jul 03 '17 at 15:31
Possible duplicate of [How do I evaluate a string expression in SQL Server?](https://stackoverflow.com/questions/24336751/how-do-i-evaluate-a-string-expression-in-sql-server) — Siyual, Jul 03 '17 at 16:41
I've found this really helpful for a particular use case. Users can request via the UI that a certain calculation is generated every day based on other daily values from a source system. So I can take their request from a table (like ValueA+ValueB*1000/Value3), sub in today's values and run the dynamic SQL to generate the results. Lovely. — Jon.Mozley, Jul 05 '22 at 12:47

score 13 · Accepted Answer · answered Jul 03 '17 at 15:29

13

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)

answered Jul 03 '17 at 15:29

Pரதீப்

91,748
19
131
172

Potential sql injection if `sumstrings` isn't sanitized. – SqlZim Jul 03 '17 at 15:38
@SqlZim - Hope sumstrings is sanitized ;) – Pரதீப் Jul 03 '17 at 15:44
@SqlZim Just for fun: I added an approach to my answer around this using XML's implicit capability to compute literals... :-D – Shnugo Jul 03 '17 at 16:00

Shnugo · Answer 2 · 2017-07-04T12:50:16.663

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

You have impressive XML skills sir.. I have seen you writing nice xml answers +1 — Pரதீப், Jul 03 '17 at 16:02

score 1 · Answer 3 · answered Jul 03 '17 at 15:29

1

You will have to query the strings and evaluate them in the program that executes the query. This is non-trivial but there are many examples of languages written in flex+bison (or other tools) that would help you evaluate the expressions if you really, really must store the expressions and not their values.

answered Jul 03 '17 at 15:29

Jeff Holt

2,940
3
22
29

@whytheq Even if it meant writing code that was more difficult to write and maintain? – Jeff Holt Jul 03 '17 at 15:34
this is just for fun – whytheq Jul 03 '17 at 15:35
1

Sorry for being off topic, but since you say it's for fun, your comment reminds me of what Dryden said to Lawrence in his office as he was about to embark upon his expedition to find Feisal. – Jeff Holt Jul 03 '17 at 15:38
(upped answer: mainly for the last comment!) – whytheq Jul 03 '17 at 15:47

SqlZim · Answer 4 · 2017-07-03T15:45:51.193

Use replace() to add a delimiter before the + and -; split the string, and sum():

In SQL Server 2016+ you can use string_split().

select 
    t.SumStrings
  , Summed = sum(convert(int,s.value))
from #theSums t
cross apply string_split(replace(replace(SumStrings,'+','|+'),'-','|-'),'|') s
group by t.SumStrings

returns:

+------------+--------+
| SumStrings | Summed |
+------------+--------+
| 1+2        |      3 |
| 1+2-3      |      0 |
+------------+--------+

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=abd084c8fe3758c29c26e29a1f9dfa36

In SQL Server pre-2016, using a CSV Splitter table valued function by Jeff Moden:

select 
    t.SumStrings
  , Summed = sum(convert(int,s.Item))
from #theSums t
cross apply delimitedsplit8K(replace(replace(SumStrings,'+','|+'),'-','|-'),'|') s
group by t.SumStrings

rextester demo: http://rextester.com/GTGT29482

returns:

+------------+--------+
| SumStrings | Summed |
+------------+--------+
| 1+2        |      3 |
| 1+2-3      |      0 |
+------------+--------+

There are many ways to split strings, this basic premise will work with any of them.

splitting strings reference:

This is very limited to operators – Pரதீப் Jul 03 '17 at 15:45 — Pரதீப், Jul 03 '17 at 15:45

score 1 · Answer 5 · answered Jul 03 '17 at 15:44

1

Another dynamic sql

DECLARE @sql VARCHAR(8000) = ''

SELECT @sql = CONCAT( @sql , ' union all select ',  ts.SumStrings)
FROM #theSums ts 

SELECT @sql = STUFF(@sql, 1,10,'')

PRINT @sql 

EXEC (@sql)

answered Jul 03 '17 at 15:44

TriV

5,118
2
10
18

2

This is logically the same as Prdp's answer and it is using a *quirky update*, something to be avoided... – Shnugo Jul 03 '17 at 15:49
@Shnugo I have started my career in 2014 from that time I see people used to say like quirky update method may fail or it will work in future versions. After 2013 two versions of sql server released still it works like a boss all the time – Pரதீப் Jul 03 '17 at 16:04

OLIVER.KOO · Answer 6 · 2017-07-03T16:22:09.940

Use a temp table. Extract the string formula from sumString and execute it with select then insert the desired value into the temp table. Do this row by row.

Set up the tables: CREATE TABLE sumString( formula varchar(20) )

CREATE TABLE temp(
    result varchar(20)
)

INSERT INTO sumString(formula)
VALUES 
('1+3'),
('1+2-3')

The solution:

DECLARE @expression varchar(20)

WHILE(EXISTS(SELECT 1 FROM sumString))

BEGIN
    SELECT TOP(1) @expression = formula 
    FROM sumString
    INSERT INTO temp
    --the key is to execute with select as a string so the string formula will be evaluate
    exec ('select '+ @exp)  
    DELETE TOP (1) FROM sumString
END

Result:

--check the result--

SELECT * FROM temp

result
4
0

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

6 Answers6

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

Remark