Finding numbers in a column and adding it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding numbers in a column and adding it

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2011-05-27 : 11:35:13

Hi Guys,

there is a column that has data like "1, 2", "1,2,3,4".
I want to take that value and add another column to the table with the total of the number.

so,

1. Create a new column.
2. Find the numbers in the source column (e.g. 1, 2, 3, 4).
3. remove the commands and get the numbers.
4. Add the numbers (e.g 10) and insert to the column created in step 1.

Does someone has a SQL handy to to this.

Much appreciated.

Many thanks,
Shiyam

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 11:37:56

Ever heard of normalisation?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 11:44:40

declare @t table (s varchar(20), i int)
insert @t select '1,2,3', 0
insert @t select '1,2', 0
insert @t select '1', 0

;with cte as
(
select distinct s, i = 1, j = CHARINDEX(',',s,1) from @t
union all
select s, i = j+1, j=charindex(',',s,j+1) from cte where j <> 0
)
,cte2 as
(
select s,n = sum(convert(int,SUBSTRING(s,i,case when j = 0 then len(s) else j-i end)))
from cte
group by s
)
update @t
set i = t2.n
from @t t
join cte2 t2
on t.s = t2.s

select * from @t

Yuk

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2011-05-27 : 12:12:50

THANKS A LOT !!! Worked like a charm!!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 19:04:43

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

>> here is a column that has data like "1, 2", "1,2,3,4". <<

NO, that is not a column by definition. A column is a scalar value. Get a book on RDBMS and SQL, so you will know what normalization is.

>> I want to take that value and add another column to the table with the total of the numbers.<<

Why? There are stinking dirty kludges to do this, but that is not your real problem.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources