Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating