Comments posted to this topic are about the item List Count
Use a tally table surely?
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,''));
Mr or Mrs. 500
@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.
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.
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,'
NULLIF(SUBSTRING(@mylist,id,CHARINDEX(',',@mylist+',',id)-ID),'') AS string
where id <= LEN(@mylist) and SUBSTRING(','+@mylist,ID,1) = ','
declare @mylist nvarchar(100), @delimiter VARCHAR(2)
set @mylist = 'A,B, C, D , 1, 2,345, EFG, H, '
set @delimiter = ','
(LEN(@mylist) - LEN(replace(@mylist,@delimiter,'')))/LEN(@delimiter)
case when right(rtrim(LTRIM(@mylist)),1) = @delimiter then 0 else 1 end itemcount
Thanks for the script.
Viewing 8 posts - 1 through 7 (of 7 total)