title: More efficent way to select number of occurences in a string
body: <p>Hi all i have the following script which is all fine and dandy and does the job, however i don't think it is very nice or efficient. Just wondering if there's a better and more efficient way of doing the same? The job is to calculate the number of occurrences of numbers in a number string and then select the total. So for example with the number '014812000', the expected outcome should be 6 as we have 4 x 0 occurrences and 2 x 1 occurrences. We are only concerned with occurrences over 1. Just to add this is being using in a user defined function.</p>
  
  <pre><code>DECLARE @Number nvarchar(50)
  SET @Number = '014812000'
  
  DECLARE @count0 int
  DECLARE @count1 int
  DECLARE @count2 int
  DECLARE @count3 int
  DECLARE @count4 int
  DECLARE @count5 int
  DECLARE @count6 int
  DECLARE @count7 int
  DECLARE @count8 int
  DECLARE @count9 int
  
  DECLARE @countTotal int
  SET @countTotal = 0
  
  SET @count0 = LEN(@Number) - LEN(REPLACE(@Number, '0', '')) 
  SET @count1 = LEN(@Number) - LEN(REPLACE(@Number, '1', '')) 
  SET @count2 = LEN(@Number) - LEN(REPLACE(@Number, '2', '')) 
  SET @count3 = LEN(@Number) - LEN(REPLACE(@Number, '3', '')) 
  SET @count4 = LEN(@Number) - LEN(REPLACE(@Number, '4', '')) 
  SET @count5 = LEN(@Number) - LEN(REPLACE(@Number, '5', '')) 
  SET @count6 = LEN(@Number) - LEN(REPLACE(@Number, '6', '')) 
  SET @count7 = LEN(@Number) - LEN(REPLACE(@Number, '7', '')) 
  SET @count8 = LEN(@Number) - LEN(REPLACE(@Number, '8', '')) 
  SET @count9 = LEN(@Number) - LEN(REPLACE(@Number, '9', '')) 
  
  IF @count0 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count0
      END
  IF @count1 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count1
      END
  IF @count2 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count2
      END
  IF @count3 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count3
      END
  IF @count4 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count4
      END
  IF @count5 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count5
      END
  IF @count6 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count6
      END
  IF @count7 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count7
      END
  IF @count8 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count8
      END
  IF @count9 &gt; 1
      BEGIN
          SET @countTotal = @countTotal + @count9
      END
  
  SELECT @countTotal
  </code></pre>