Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List Count Expand / Collapse
Author
Message
Posted Saturday, March 05, 2011 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 2:52 PM
Points: 2, Visits: 49
Comments posted to this topic are about the item List Count
Post #1073758
Posted Monday, March 07, 2011 1:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:54 AM
Points: 282, Visits: 501
Use a tally table surely?
Post #1073984
Posted Monday, March 07, 2011 2:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 3:18 AM
Points: 276, Visits: 524
This is simpler...

declare @mylist nvarchar(100);
set @mylist = 'A,B, C, D , 1, 2,345, EFG, H,';


declare @delim varchar(2)=',';
select LEN(@mylist) - LEN(replace(@mylist,@delim,''));

Post #1073988
Posted Monday, March 07, 2011 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:54 AM
Points: 42, Visits: 60
@paul_ramster - My thoughts exactly. That is the way I've always done it in SQL and other languages. The only reason you'd need to use a loop to get a count in a list like this is if you had a text qualifier, where when the delimiter falls within the text qualifier, it is not a delimiter.
Post #1074128
Posted Monday, March 07, 2011 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 2:52 PM
Points: 2, Visits: 49
You're exactly right. I was lost in a solution that considered delimiters within text qualifiers and ended up with something far more complex than was necessary.
Post #1074226
Posted Wednesday, April 27, 2011 4:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 1:56 AM
Points: 593, Visits: 371


DECLARE @Numbertable table
(
ID INT PRIMARY KEY

)


INSERT INTO @Numbertable
select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)
from sys.objects si,sys.objects s

declare @mylist nvarchar(100)
set @mylist = 'A,B, C, D , 1, 2,345, EFG, H,'

select COUNT(string)
from
(
select
NULLIF(SUBSTRING(@mylist,id,CHARINDEX(',',@mylist+',',id)-ID),'') AS string
from @Numbertable
where id <= LEN(@mylist) and SUBSTRING(','+@mylist,ID,1) = ','
)d


Post #1099293
Posted Thursday, July 14, 2011 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 12:34 AM
Points: 3, Visits: 133
declare @mylist nvarchar(100), @delimiter VARCHAR(2)
set @mylist = 'A,B, C, D , 1, 2,345, EFG, H, '
set @delimiter = ','
select @mylist list,
(LEN(@mylist) - LEN(replace(@mylist,@delimiter,'')))/LEN(@delimiter)
+
case when right(rtrim(LTRIM(@mylist)),1) = @delimiter then 0 else 1 end itemcount
Post #1141609
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse