SQL command (SQL server 7)

[PhilJeary]

Wasnt sure where to put this question, but VB users sometimes know a bit of SQL:

Just a quick "know it or you dont question".

Is there a command to count the number of instances of a specified character or string in a field?

Thought i'd ask before I spend the time writing my own, however, if I do ill post it here.
Any help would be much appreciated.

Additional: Saying that, I can't seem to find the sql equivalent of the instr command to make my own function either!

[WendellB]

Not sure if SQL 7 supports includes this function, but SQL 2000 has the CHARINDEX(StringToFind,Column[,StartIndex]) function which does much the same thing as Instr().

[PhilJeary]

Thanks Wendel, it works great.

So far i've been unable to find a command to return the number of instances so i'm going to make a user defined function that uses CharIndex in a loop to count them one by one. Will post it if I succeed.

[PhilJeary]

SOLUTION:

SQL function. In a view, call it using Cinstances(fieldname,"searchtext") as NumberOfInstances.
Might need a db. in front of Cinstances but I can't remember.
This returns the number of times the search criteria appears in each row of the fieldname column.

CREATE FUNCTION CInstances(@VarData as varchar(35), @searchstr as varchar(35))
RETURNS integer AS
BEGIN
DECLARE @result AS integer, @pos1 as integer, @VarDataLen as integer
Set @VarDataLen = len(@VarData)
SET @Result = 0
set @pos1 = 1
while @pos1 < @VarDataLen
BEGIN
if charindex(@searchstr,@vardata ,@pos1) >0
Begin
set @pos1 = @pos1 + charindex(@searchstr,@vardata ,@pos1)
set @result = @result +1
end
else set @pos1 = @vardatalen
END
return @result
END