This is an old thread but I would suggest a different approach. It might get give better results performance wise.
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION [dbo].[ProperCase]
(
@TargetStr CHAR(1),
@SearchedStr VARCHAR(8000),
@Occurrence INT = 2
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@SearchedStr),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteAppearances(N, rn) AS( SELECT N, ROW_NUMBER() OVER (ORDER BY N) rn
FROM cteTally
WHERE SUBSTRING(@SearchedStr, N, 1) = @TargetStr
)
SELECT TOP 1 CASE WHEN rn >= @Occurrence THEN N ELSE 0 END N
FROM cteAppearances
WHERE rn = @Occurrence OR rn < @Occurrence
ORDER BY N DESC
GO
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION [dbo].[ProperCase2]
(
@TargetStr VARCHAR(8000),
@SearchedStr VARCHAR(8000),
@Occurrence INT = 2
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@SearchedStr),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteAppearances(N, rn) AS( SELECT N, ROW_NUMBER() OVER (ORDER BY N) rn
FROM cteTally
WHERE SUBSTRING(@SearchedStr, N, LEN(@TargetStr)) = @TargetStr
)
SELECT TOP 1 CASE WHEN rn >= @Occurrence THEN N ELSE 0 END N
FROM cteAppearances
WHERE rn = @Occurrence OR rn < @Occurrence
ORDER BY N DESC
GO