CODEWITHSUNDEEP
sqlsql-server
Dave Sexton
Dave Sexton

Reputation: 11188

How to sum numbers in a delimited string using SQL Server

I have a string containing numbers delimited by a pipe like so 23|12|12|32|43.

Using SQL I want to extract each number, add 10 and then sum to get a total.

Upvotes: 1

Views: 2897

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269733

Here is another alternative:

declare @str nvarchar(max) = '23|12|12|32|43';

set @str = 'select '+replace(@str, '|', '+');

exec(@str);

Upvotes: 3

Ivan Golović
Ivan Golović

Reputation: 8832

Here is another way of doing it:

DECLARE @s VARCHAR(1000) = '23|12|12|32|43'

SELECT CAST('<root><e>' + REPLACE(@s, '|', '</e><e>') + '</e></root>' AS XML)
        .value('sum(/root/e) + count(/root/e) * 10', 'INT')

This uses casting to XML data type and functions provided by it.

I posted this just as an example, your approach has a much better performance.

Upvotes: 2

Dave Sexton
Dave Sexton

Reputation: 11188

The answer using a recursive common table expression:

WITH cte AS (
  SELECT
    '23|12|12|32|43' + '|' AS string
    ,0 AS total
  UNION ALL
  SELECT
    RIGHT(string, LEN(string) - PATINDEX('%|%', string))
    ,CAST(LEFT(string, PATINDEX('%|%', string) - 1) AS INT) + 10
  FROM cte
  WHERE PATINDEX('%|%', string) > 0

)
SELECT SUM(total) AS total FROM cte

As the recursion terminator I have put in a check to see if any more pipes exist in the string, however this then missed the last element which I have got around by concatenating an extra pipe on to the end of my original string, I think there is probably a better way to express the WHERE clause.

Upvotes: 2

Related Questions