>Does anyone know how to do the equivalent of the VFP occurs function in SQL Server 7.
>
>For example, in my data field bigstring contains 'AAABCCCDD'
>
>occurs('A',bigstring)=3
>occurs('ABC',bigstring)=1
>
>Thanks
There's no direct equivelent that I know of. You will have to use a loop and patindex and right to get that. Here you go, I just wrote this, seems to work. It is not case sensitive.

Create Procedure sp_occurs
(@search varchar(100)
,@textstring varchar(5000)
,@occurs int output)

AS

declare @position int

set @occurs = 0

While len(@textstring) > 0
begin

set @position = patindex('%'+@search+'%',@textstring)

if @position = 0
set @textstring = ''
else
begin
set @occurs = @occurs + 1
set @textstring = substring(@textstring,@position+len(@search),len(@textstring)-len(@search))
end

end

Return