How to select the next row so that it returns 3? SELECT '1 + 2'

Question

In the column "SumStrings" of the table, I have rows like "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 can I choose from this table to give me the results of these amounts? those. if the table had only the two above rows, then the result of the selection should be 3 and 0

This question The string expression that will be evaluated by number is mainly aimed at creating a function, I would just like a simple script that selects from a table

Answer 1

Here is one way: dynamicsql

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

... :

Prdp , SQL-

XML a - backdraw! - . SQL, :

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

CURSOR, @Prdp:

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;

/ divisor div

Answer 3

, . , , flex + bison ( ), , , .

Answer 4

replace(), + -; sum():

---

SQL Server 2016+ 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

:

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

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

---

SQL Server 2016, CSV Splitter, :

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

: http://rextester.com/GTGT29482

:

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

, .

:

• Tally OH! SQL 8K "CSV Splitter" - Jeff Moden
• : -
• - -
• string_split() in SQL Server 2016: Follow-Up # 1 - Aaron Bertrand
• Step-by-step workaround for ** string_split()**- Solomon Rutzky

Answer 5

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)

Answer 6

Use a temporary table. Extract the string formula from sumString and execute it with select, and then paste the desired value into the temp table. Do it line by line.

Customize Tables:   CREATE TABLE sumString (Formula Varchar (20))

CREATE TABLE temp(
    result varchar(20)
)

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

Decision:

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