March 5, 2011 at 12:34 pm
Comments posted to this topic are about the item List Count
March 7, 2011 at 1:26 am
Use a tally table surely?
March 7, 2011 at 2:04 am
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,''));
March 7, 2011 at 6:53 am
@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.
March 7, 2011 at 8:59 am
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.
April 27, 2011 at 4:10 am
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
July 14, 2011 at 4:51 am
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
May 17, 2016 at 6:35 am
Thanks for the script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply