Equivalent Oracle's INSTR Function in SQL server

,

You can either use the function as a simple select statement such as

SELECT *
FROM [dbo].[INSTR] ('Hello World','l',1,1) 
in addition you can use at in cross /outer apply clause
select t.* , f.Loc
from <table> as t
CROSS APPLY dbo.INSTR (T.<Column>, <strToSearch> , <StartPosition> , <Occurance#>) f
CREATE FUNCTION [dbo].[INSTR] 
(

@str VARCHAR(8000),
@Substr VARCHAR(1000),
@start INT ,
@Occurance INT
)
RETURNS TABLE 
AS 
RETURN

	WITH Tally (n) AS
	(
		SELECT TOP (LEN(@str)) ROW_NUMBER()  OVER (ORDER BY (SELECT NULL)) 
		FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
		CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
		CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
		CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
	)

, Find_N_STR as
	(
	SELECT 
	CASE WHEN DENSE_RANK() OVER(PARTITION BY @Substr ORDER BY (CHARINDEX(@Substr ,@STR ,N))) = @Occurance 
		 THEN MAX(N-@start +1) OVER (PARTITION BY CHARINDEX(@Substr ,@STR ,N) ) 
		 ELSE 0 
		 END [Loc]
	FROM Tally
	WHERE CHARINDEX(@Substr ,@STR ,N) > 0 
	)

	SELECT Loc= MAX(Loc) 
	FROM Find_N_STR
	WHERE Loc > 0 
GO

/*
SELECT Loc
FROM [dbo].[INSTR] ('Hello World','l',1,1) --3

SELECT Loc
FROM [dbo].[INSTR] ('Hello World','l',3,3) --8
*/

Rate

5 (2)

Share

Share

Rate

5 (2)