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.
-
4You shouldn't be storing delimited values in a single column. You should plan a re-design of your data modeluser330315– user3303152013-03-27 10:39:10 +00:00Commented Mar 27, 2013 at 10:39
Add a comment
|
3 Answers
Here is another alternative:
declare @str nvarchar(max) = '23|12|12|32|43';
set @str = 'select '+replace(@str, '|', '+');
exec(@str);
Comments
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.
Comments
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.
