You can either use the function as a simple select statement such as
2018-03-08
1,171 reads
You can either use the function as a simple select statement such as
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 */