Obtener la enésima ocurrencia de una cadena en SQL Server

El obtener el número de ocurrencia de una cadena dentro de otra, está más limitado en SQL Server que en Oracle. Los que vengan de una base de datos Oracle echarán en falta poder indicar el número de ocurrencia de la cadena que se desea obtener.

En Oracle, para buscar una cadena dentro de otra, se puede usar la función INSTR, la cual tiene la siguiente sintaxis:

INSTR(Cadena_entera,Fragmento_a_buscar, pos_inicial, núm_ocurrencia)

Como se observa se puede indicar el número de ocurrencia en el último parámetro, por lo que en una simple instrucción lo tendríamos. Sin embargo, en SQL Server no existe este parámetro en la función homónima:

CHARINDEX(cadena_a_buscar, cadena_entera, posicion_inicial)

Para suplir esta falta, para obtener la enésima ocurrencia de una cadena en SQL Server se puede utilizar la siguiente función:

CREATE FUNCTION [dbo].[N_Ocurrencia]
(
@Cadena varchar(1000),
@CadenaABuscar varchar(1000),
@Ocurrencia int
)
 
RETURNS int
 
as
BEGIN
    DECLARE @pos int, @contador int, @res int
 
    set @pos = CHARINDEX(@Cadena, @CadenaABuscar)
    set @contador = 1
 
    IF @Ocurrencia = 1 set @res = @pos
    ELSE
       BEGIN
           WHILE (@contador < @Ocurrencia)
           BEGIN
             select @res = CHARINDEX(@Cadena, @CadenaABuscar, @pos + 1)
             set @contador = @contador + 1
             set @pos = @res
           END
        END
    RETURN(@res)
END

La llamada a la misma podría ser:

SELECT @pos = [dbo].[N_Ocurrencia] (cadena_a_buscar, cadena_entera, num_ocurrencia)