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, July 29, 2013 2:54 PM
Points: 2, Visits: 50
Comments posted to this topic are about the item List Count
Post #1073758
Posted Monday, March 07, 2011 1:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:32 AM
Points: 351, Visits: 615
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, January 17, 2014 6:38 AM
Points: 278, Visits: 534
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: Friday, February 21, 2014 8:08 AM
Points: 42, Visits: 74
@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, July 29, 2013 2:54 PM
Points: 2, Visits: 50
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:08 AM
Points: 751, Visits: 587


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




Regards,
Mitesh OSwal
+918698619998
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 14, 2014 2:32 AM
Points: 4, Visits: 154
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