Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
declare @s nvarchar(max)='1. Some data - 32180000' declare @separator char(1)=space(1); SELECT cast('<root><r>'+replace(@s,@separator,'</r><r>') + '</r></root>' as xml).query('<root> { for $x in /root/r return if (xs:int($x) instance of xs:int) then $x else () (: filter out non-integers :) } </root>').query('sum(/root/r/text())').value('.','int') as result
Amazing, the sum()
converts its calculation result to a scientific notation trying to make it more compact.
Useful link: type-casting-rules-in-xquery
Please try the following solution.
SQL
DECLARE @s NVARCHAR(MAX) = N'1. Some data - 32180000';
DECLARE @separator CHAR(1) = SPACE(1);
SELECT CAST('<root><r>' +
REPLACE(@s, @separator, '</r><r>') + '</r></root>' AS XML)
.query('<root>
{
for $x in /root/r return if (xs:int($x) instance of xs:int) then
$x else () (: filter out non-integers :)
}
</root>')
.query('sum(/root/r/text()) cast as xs:int?').value('.','INT') as result;
If you have really gigantic numbers you can handle them as follows:
.query('sum(/root/r/text()) cast as xs:unsignedLong?').value('.','BIGINT') as result;
Output
+----------+
| result |
+----------+
| 32180000 |
+----------+