0

Given a variable that contains a series of 0's and 1's how can I count the number of each in the variable?

Here's an example:

SET @AnswerGridCorrect = '0010010';

What I need to do is to return a comment into a variable called @Hint such that it will contain:

"Select 2 out of 7 choices"
Samantha J T Star
  • 30,952
  • 84
  • 245
  • 427
  • 1
    possible duplicate of [How can you find the number of occurrences of a particular character in a string using sql?](http://stackoverflow.com/questions/287373/how-can-you-find-the-number-of-occurrences-of-a-particular-character-in-a-string) – CodingIntrigue Feb 16 '15 at 12:45

3 Answers3

2

Use this:

DECLARE @AnswerGridCorrect VARCHAR(MAX)
DECLARE @Question VARCHAR(MAX)

SET @AnswerGridCorrect = '0010010';

SET @question = 'Select ' + CAST(LEN(@AnswerGridCorrect) - LEN(REPLACE(@AnswerGridCorrect, '1', '')) AS VARCHAR(MAX)) +
                ' out of ' + CAST(LEN(@AnswerGridCorrect) AS VARCHAR(MAX)) + ' choices'

SELECT @Question

Output:

Select 2 out of 7 choices

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
1

You will need to loop through each position and test if it is a 0 or 1. For example:

while i <= 7 begin
    if substr(@AnswerGridCorrect,i,1) == 1
      set count = count + 1
    set i = i + 1
end

Note the above is an incomplete example but it should give you an idea.

Also, see https://msdn.microsoft.com/en-us/library/ms187748.aspx

Mark Wagoner
  • 1,729
  • 1
  • 13
  • 20
0

Since you only have 0 and 1's this is fairly easy doable :

The best solution would be to use a bit-wise AND (something along the lines of)

public bool CheckBit(byte b, int bitNumber)
    {
        var bit = (b & (1 << bitNumber - 1)) != 0;
        return bit;
    }

If you pass all of the 0 and 1's one by one (as bitNumber) here you can count how many return true (if both are 1) and how many return false (if the number passed is 0) and even find out which one are set to 0 and which ones are set to 1.

Hope this helps :)

Johan
  • 262
  • 1
  • 15