List Count

  • Chad Casady

    Valued Member

    Points: 62

    Comments posted to this topic are about the item List Count

  • julian.fletcher

    SSCrazy

    Points: 2627

    Use a tally table surely?

  • paul_ramster

    Ten Centuries

    Points: 1382

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

  • jmpatchak

    SSC-Addicted

    Points: 457

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

  • Chad Casady

    Valued Member

    Points: 62

    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.

  • Mitesh Oswal

    SSCrazy

    Points: 2798

    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

  • suryakant_ker

    Grasshopper

    Points: 22

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply