List Count

  • 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,''));

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




    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)




    NULLIF(SUBSTRING(@mylist,id,CHARINDEX(',',@mylist+',',id)-ID),'') AS string

    from @Numbertable

    where id <= LEN(@mylist) and SUBSTRING(','+@mylist,ID,1) = ','


    Mitesh OSwal

  • 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

  • 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