Can I get a List Length w/ SQL

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

SQL Server 2000 Forums

Transact-SQL (2000)

Can I get a List Length w/ SQL

Author

Topic

pmolaro
Starting Member

1 Post

Posted - 2005-07-26 : 16:27:28

I am using Transact SQL to pull some data for a report. One of my columns of data has a comma separated list of pkeys from another table. I want to get a List Length (ListLen() in Coldfusion) of the list in each column. Is there ANY way to do all that in Transact SQL??

So a few columns of data might look like this (one line is one record):
23, 234, 873, 1047, 2312, 45
54, 651, 23, 12
765
987, 456, 21

So I eventually want a SUM() of the number of items. So using the dummy data above, a sum of the items would be 14 (Rec1 has 6 items, Rec2 has 4 items, Rec3 has 1 items, Rec4 has 3 items).

------------------------------------
Phil Molaro - pmolaro@gmail.com
------------------------------------

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-26 : 16:42:40

Here's one way:


declare @tb Table (list varchar(50))
insert @tb
select '23, 234, 873, 1047, 2312, 45' union
select '54, 651, 23, 12' union
select '765' union
select '987, 456, 21'

select	sum(len(list)-len(replace(list,',',''))+1) as sumListLen
from	@tb

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources