How to get count of available guid from a comma separated string of guid

Question

Declare @i int;
declare @stringOfGuids nvarchar(max)='''70173C2D-0B8E-4043-BD14-665D5DCCF112'',''B0B7445C-DF50-4D49-BD4E-B74958FB0618''
,''70173C2D-0B8E-4043-BD14-665D5DCCF112''';

I have this string and I need to count the number of available guid which is three.

How can I get it in a Integer variable?

Something like this ?

select @i = count(@stringOfGuids)

but it returns 1, I need three as available guids are 3.

one way is to count the number of " characters in the string, then divide it by 2 — Cato, Jul 27 '16 at 08:45

score 4 · Answer 1 · edited May 23 '17 at 12:22

4

Count the number of commas and add 1.

SELECT Len(@stringOfGuids ) - Len(Replace( @stringOfGuids , ',', '')) + 1

The counting is from this answer.

edited May 23 '17 at 12:22

Community

1
1

answered Jul 27 '16 at 08:45

Marko Juvančič

5,792
1
25
41

that's ingenious! He might want to count the quotes that way though, cos he's got a colon at the end I think - oh yeah sorry, add 1 – Cato Jul 27 '16 at 08:55
yes, excellent Marko, it worked for me ,, thnx alot..good decent solution.. – Rajesh Jul 27 '16 at 08:56

score 1 · Accepted Answer · answered Jul 27 '16 at 08:51

Declare @i int;

declare @stringOfGuids nvarchar(max)='''70173C2D-0B8E-4043-BD14-665D5DCCF112'',''B0B7445C-DF50-4D49-BD4E-B74958FB0618'' ,''70173C2D-0B8E-4043-BD14-665D5DCCF112''';

declare @cnt int = 0;
declare @pos int = 1;

while charindex('''', @stringOfGuids, @pos) > 0
    SELECT @pos =  charindex('''', @stringOfGuids, @pos) + 1, @CNT = @CNT + 1;



SELECT @CNT / 2;

How to get count of available guid from a comma separated string of guid

2 Answers2