Lynn Pettis (4/19/2013)
Like this:
CREATE FUNCTION [dbo].[ifn_NumOccurrences]
(
@sourceString varchar(1000),
@searchString varchar(10)
)
RETURNS TABLE
AS
return
SELECT numTimes = (DATALENGTH(@sourceString) - DATALENGTH(REPLACE(@sourceString COLLATE Latin1_General_BIN2, @searchString, ''))) / DATALENGTH(@searchString);
GO
But people have to be aware of the problem that if the search string ends with one or more spaces but also has other characters, this will sometimes return a result too low by 1 (whenever the final ocurrence of the search string in the sourcestring is followed only by spaces), whether or not it's done as an iTVF.
Tom