scarpacci scarpacci - 5 months ago 26
SQL Question

SQL Server Equivalent to ORACLE INSTR

I wanted to know if in SQL Server there is an equivalent to the Oracle

INSTR
function?

I know that there is
CHARINDEX
and
PATINDEX
, but with the Oracle version I can also specify the Nth appearance of the character(s) I am looking for.


Oracle
INSTR
:

instr( string1, string2 [, start_position [, **nth_appearance** ] ] )



The
CHARINDEX
almost gets me there, but I wanted to have it start at the
nth_appearance
of the character in the string.

Answer

You were spot on that nth_appearance does not exist in SQL Server.

Shamelessly copying a function (Equivalent of Oracle's INSTR with 4 parameters in SQL Server) created for your problem (please note that @Occurs is not used the same way as in Oracle - you can't specify "3rd appearance", but "occurs 3 times"):

CREATE FUNCTION udf_Instr
    (@str1 varchar(8000), @str2 varchar(1000), @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