More efficent way to select number of occurences in a string

Question

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.

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 > 1
    BEGIN
        SET @countTotal = @countTotal + @count0
    END
IF @count1 > 1
    BEGIN
        SET @countTotal = @countTotal + @count1
    END
IF @count2 > 1
    BEGIN
        SET @countTotal = @countTotal + @count2
    END
IF @count3 > 1
    BEGIN
        SET @countTotal = @countTotal + @count3
    END
IF @count4 > 1
    BEGIN
        SET @countTotal = @countTotal + @count4
    END
IF @count5 > 1
    BEGIN
        SET @countTotal = @countTotal + @count5
    END
IF @count6 > 1
    BEGIN
        SET @countTotal = @countTotal + @count6
    END
IF @count7 > 1
    BEGIN
        SET @countTotal = @countTotal + @count7
    END
IF @count8 > 1
    BEGIN
        SET @countTotal = @countTotal + @count8
    END
IF @count9 > 1
    BEGIN
        SET @countTotal = @countTotal + @count9
    END

SELECT @countTotal

Answer 1

You can use a number table to split the number to rows. Here I use master..spt_values.

select sum(C)
from (
       select count(*) as C
       from master..spt_values as N
       where N.type = 'P' and
             N.number between 1 and len(@Number)
       group by substring(@Number, N.Number, 1)
       having count(*) > 1
     ) as T      

Answer 2

This would be able to handle your problem even if the number wasn't numeric, it can count any occurence of characters where more than 1 identical character exists.

It was made for a table characters.

declare @t table(number nvarchar(max))
insert @t values ('014812000')
insert @t values ('0148120001')
insert @t values ('0148120001aa')

;with a as
(
select number n, 0 i from @t
union all
select replace(n, cast(n as char(1)), ''), 
case when replace(n, cast(n as char(1)), '') = stuff(n,1,1,'') then i else 
i + cast(len(n) - len(replace(n, cast(n as char(1)), '')) as int)
end
from a
where n <> ''
)
select i from a where n = ''

If you only want for one specific number this is how you could use it:

declare @Number nvarchar(max) 
set @Number = '014812000'  

;with a as
(
select @number n, 0 i
union all
select replace(n, cast(n as char(1)), ''), 
case when replace(n, cast(n as char(1)), '') = stuff(n,1,1,'') then i else 
i + cast(len(n) - len(replace(n, cast(n as char(1)), '')) as int)
end
from a
where n <> ''
)
select i from a where n = ''

Answer 3

take a look at this. Its using a custom function.

FUNCTION dbo.com_CountString(@Input nVarChar(max), @SearchString nVarChar(1000))
RETURNS INT
BEGIN
DECLARE @Count INT, @Index INT, @InputLength INT, @SearchLength INT
DECLARE @SampleString INT

if @Input is null or @SearchString is null
    return 0

SET @Count = 0
SET @Index = 1
SET @InputLength  = LEN(@Input)
SET @SearchLength = LEN(@SearchString)

if @InputLength = 0 or @SearchLength = 0 or @SearchLength > @InputLength
    return 0

WHILE @Index <= @InputLength - @SearchLength + 1
BEGIN
    IF SUBSTRING(@Input, @Index, @SearchLength) = @SearchString
    BEGIN
        SET @Count = @Count + 1
        SET @Index = @Index + @SearchLength
    END
    ELSE
        SET @Index = @Index + 1
END

RETURN @Count
END