473,322 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Sum Comma Seperated string

I have a table a which has a column x
x has values 12,34,56,78 .I need an sql query which can sum the CS values in that row like 12+34+56+78 and need to display the sum.Please advise me as I am desperate in searching it...
Thanks in Anticipation...
Oct 6 '06 #1
2 11289
Expand|Select|Wrap|Line Numbers
  1. You can use this UDF:
  2.  
  3. CREATE FUNCTION SumCSV(@CSV AS VARCHAR(1000))
  4. RETURNS INT
  5. AS
  6. BEGIN
  7.  
  8. DECLARE @Sum INT, @CurNumber INT, @CommaIndex INT
  9. SET @Sum = 0
  10.  
  11. DECLARE @CurNumStr VARCHAR(20)
  12.  
  13.  
  14. WHILE LEN(@CSV) > 0
  15. BEGIN
  16.  
  17.     SET @CommaIndex = CHARINDEX(',', @CSV)
  18.     IF @CommaIndex = 0 SET @CommaIndex = LEN(@CSV)+1
  19.     SET @CurNumStr = SUBSTRING(@CSV, 1, @CommaIndex-1)
  20.     SET @CSV = SUBSTRING(@CSV, @CommaIndex+1, LEN(@CSV))
  21.     IF ISNUMERIC(@CurNumStr)=1
  22.     BEGIN
  23.         SET @CurNumber = CONVERT(INT, @CurNumStr)
  24.         SET @Sum = @Sum + @CurNumber
  25.     END
  26. END
  27. RETURN @Sum
  28. END
Then your query would be:

SELECT dbo.SumCSV(CSVColumn)
FROM table
Oct 6 '06 #2
Another Way Of Doing This Is To Create A Generic Function That Puts The Values In A Table and Sum The Resulting Table.

i.e. SELECT SUM(*) FROM dbo.ParseByComma('1,2,3,4,5,6,7,8')

/************************************************** ***************
**** Parse A Comma Delimited String Into A Table
************************************************** ***************/
CREATE FUNCTION dbo.ParseByComma (
@String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
VarSubString VARCHAR(10)
)
AS
BEGIN
DECLARE @intPos INT,
@SubStr VARCHAR(10)

-- Remove All Spaces
SET @String = REPLACE(@String, ' ','')
-- Find The First Comma
SET @IntPos = CHARINDEX(',', @String)
-- Loop Until There Is Nothing Left Of @String
WHILE @IntPos > 0
BEGIN
-- Extract The String
SET @SubStr = SUBSTRING(@String, 0, @IntPos)
-- Insert The String Into The Table
INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
-- Remove The String & Comma Separator From The Original
SET @String = REPLACE(@String, @SubStr + ',', '')
-- Get The New Index To The String
SET @IntPos = CHARINDEX(',', @String)
END
-- Return The Last One
INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END
Nov 13 '06 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Pmb | last post by:
I'm trying to learn the syntax for initializing objects in a comma separated list. Below is an example program I wrote to learn how to do this (among other things). While I understand how to...
11
by: Craig Keightley | last post by:
I have a mysql database with a list of companies who supply specific products tblSuppliers (simplified) sID | sName | goodsRefs 1 | comp name | 1,2,3,4,5 2 | company 2 | 2,4
3
by: Gary Smith | last post by:
Hi, I've got a field that contains a list of rooms. In most cases, this contains a single ID. However, under some circumstances, the field may contain a list of two IDs which are broken by a...
11
by: Shawn Odekirk | last post by:
Some code I have inherited contains a macro like the following: #define setState(state, newstate) \ (state >= newstate) ? \ (fprintf(stderr, "Illegal...
7
by: Sick | last post by:
My application populates a ListBox from a .TXT file. In the textfile there are prices with both dots as well as comma's for decimal indication. Example: 1234990; xg-tr-45; 1700,50; 0 2662666;...
3
by: dfetrow410 | last post by:
I need make a comma seperated list, but whwn I build the list I get a comma at the end. How do I remove it? foreach (ListItem lst in REIPropertyType.Items) { if (lst.Selected == true) {...
1
by: CJK | last post by:
this is what i have so far: #include <iostream> #include <string> #include <vector> #include <fstream> using namespace std; class Person {
2
nehashri
by: nehashri | last post by:
i hv a database in access wid Asp as front end. ven given a word in search command(of the front end) it shud go to a table in which each field has words serated by comma. each word shud b checked...
0
by: Kristi | last post by:
I need to create a CL program that will take a PF, and create a tab delimited file that has comma seperated column headings as the first record. I know i can use cpytostmf/cpytoimpf to create the...
1
by: (2b|!2b)==? | last post by:
I have the following line in my code, which is supposed to skip commas and white space and to read a comma seperated value string into the appropriate variables: sscanf(temp.c_str(),...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.