SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Chad Casady / List Count / Latest Posts

RE: List Count

suryakant_ker — Thu, 14 Jul 2011 04:51:29 GMT

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

RE: List Count

Mitesh Oswal — Wed, 27 Apr 2011 04:10:33 GMT

[code="sql"]DECLARE @Numbertable table( ID INT PRIMARY KEY )INSERT INTO @Numbertableselect TOP 1000 ROW_NUMBER() OVER(order by si.object_id)from sys.objects si,sys.objects sdeclare @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 stringfrom @Numbertablewhere id <= LEN(@mylist) and SUBSTRING(','+@mylist,ID,1) = ',')d[/code]

RE: List Count

Chad Casady — Mon, 07 Mar 2011 08:59:51 GMT

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.

RE: List Count

jmpatchak — Mon, 07 Mar 2011 06:53:33 GMT

@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.

RE: List Count

paul_ramster — Mon, 07 Mar 2011 02:04:32 GMT

This is simpler...[code="sql"]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,''));[/code]

RE: List Count

julian.fletcher — Mon, 07 Mar 2011 01:26:05 GMT

Use a tally table surely?

List Count

Chad Casady — Sat, 05 Mar 2011 12:34:49 GMT

Comments posted to this topic are about the item [B]List Count[/B]