Starting at a user defined location in a string to find the nth occurance of a target string's starting location
EDIT: OK, I got Jay's set based method to work. And I mistook Jay for Jay White (Page47) but I was wrong (but I should be given a pass, because it's just like what Mr. 47 would have written). Now we have to ask, Jay who?
Not anymore, thanks Jay
Anyway Thanks Jay. Just as a note: This requires a numbers table. The concept of using this to eliminate recursion and cursors is sort of akin to turning a light on after stumbling around in the dark for so long (and even stumbling around in the light, since I couldn't employee this concept in my original shot at this). The reasons to not use set based processing is becoming smaller and smaller on a daily basis. Anyway, enjoy.
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE numbers(n int)
GO
DECLARE @x int
SET @x = 1
WHILE @x < 1001
BEGIN
INSERT INTO numbers(n) SELECT @x
SELECT @x = @x + 1
END
GO
CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(8000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @instr int
SELECT TOP 1 @instr = instr
FROM (
SELECT d.*
, (SELECT COUNT(1)
FROM (SELECT DISTINCT CHARINDEX(@str1,SUBSTRING(@str2,@start,LEN(@str2)-@start+1),n) AS instr
FROM numbers
WHERE n <= LEN(@str2))AS d2
WHERE d2.instr <= d.instr) AS ct
FROM (SELECT DISTINCT CHARINDEX(@str1,SUBSTRING(@str2,@start,LEN(@str2)-@start+1),n) AS instr
FROM numbers
WHERE n <= LEN(@str2)) AS d
) AS d
GROUP BY instr,ct
HAVING ct <= @occurs
ORDER BY ct DESC
RETURN @instr
END
GO
SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO
My Original shot:
CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(8000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @Found int, @LastPosition int
SET @Found = 0
SET @LastPosition = @start - 1
WHILE (@Found < @Occurs)
BEGIN
IF (CHARINDEX(@str1, @str2, @LastPosition + 1) = 0)
BREAK
ELSE
BEGIN
SET @LastPosition = CHARINDEX(@str1, @str2, @LastPosition + 1)
SET @Found = @Found + 1
END
END
RETURN @LastPosition
END
GO
SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO
DROP FUNCTION udf_Instr
GO