Buscar el índice de la última ocurrencia de una sub-cadena usando T-SQL

Manera directa? No, pero he usado el reverso. Literalmente.

En rutinas anteriores, para encontrar la última ocurrencia de una cadena dada, usé la función REVERSE (), seguida de CHARINDEX, seguida de nuevo por REVERSE para restaurar el orden original. Por ejemplo:

SELECT
   mf.name
  ,mf.physical_name
  ,reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
 from sys.master_files mf

Muestra cómo extraer los nombres reales de los archivos de la base de datos de sus "nombres físicos", sin importar cuán profundamente anidados estén en subcarpetas. Esto busca solo un carácter (la barra invertida), pero puede construir sobre esto para cadenas de búsqueda más largas.

El único inconveniente es que no se qué tan bien funcionará esto en los tipos de datos de TEXTO. He estado en SQL 2005 desde hace unos años, y ya no estoy familiarizado con el trabajo con TEXTO?pero me parece recordar que podría utilizar IZQUIERDA y DERECHA en él?

Philip

La forma más sencilla es....

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))

Si está utilizando Sqlserver 2005 o superior, usar la función REVERSE muchas veces es perjudicial para el rendimiento, el código inferior es más eficiente.

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

-- Shows text before last slash
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath))) AS Before
-- Shows text after last slash
SELECT RIGHT(@FilePath, CHARINDEX(@FindChar,REVERSE(@FilePath))-1) AS After
-- Shows the position of the last slash
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt

Esto funcionó muy bien para mí.

REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))

Pregunta antigua pero todavía válida, así que heres lo que he creado basado en la información proporcionada por otros aquí.

create function fnLastIndexOf(@text varChar(max),@char varchar(1))
returns int
as
begin
return len(@text) - charindex(@char, reverse(@text)) -1
end

REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))  

Funcionó mejor para mí

Hmm, sé que este es un hilo viejo, pero una tabla de conteo podría hacer esto en SQL2000 (o cualquier otra base de datos):

DECLARE @str CHAR(21),
        @delim CHAR(1)
 SELECT @str = 'Your-delimited-string',
        @delim = '-'

SELECT
    MAX(n) As 'position'
FROM
    dbo._Tally
WHERE
    substring(@str, _Tally.n, 1) = @delim

Una tabla de recuento es solo una tabla de números incrementales.

El substring(@str, _Tally.n, 1) = @delim obtiene la posición de cada delimitador, luego solo obtiene la posición máxima en ese conjunto.

Las tablas de conteo son increíbles. Si no los ha usado antes, hay un buen artículo sobre SQL Server Central (Reg gratis, o simplemente use Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com)).

*EDIT: Removed n <= LEN(TEXT_FIELD), ya que no puedes usar LEN() en el tipo de TEXTO. Mientras el substring(...) = @delim permanece, aunque el resultado sigue siendo correcto.

Invierta tanto la cadena como la subcadena, luego busque la primera ocurrencia.

Me doy cuenta de que esta es una pregunta de hace varios años, pero...

En Access 2010, puede usar InStrRev() para hacer esto. Espero que esto ayude.

Sé que será ineficiente, pero ¿ha considerado enviar el campo text a varchar para que pueda usar la solución proporcionada por el sitio web que encontró? Sé que esta solución crearía problemas, ya que podría truncar el registro si la longitud en el campo text desbordara la longitud de su varchar (por no mencionar que no sería muy eficiente).

Dado que sus datos están dentro de un campo text (y está utilizando SQL Server 2000), sus opciones son limitadas.

Si desea obtener el índice del último espacio en una cadena de palabras, puede usar esta expresión RIGHT (name, (CHARINDEX (' ', REVERSE (name), 0)) para devolver la última palabra de la cadena. Esto es útil si desea analizar el apellido de un nombre completo que incluya iniciales para el primer y /o segundo nombre.

@indexOf = <whatever characters you are searching for in your string>

@LastIndexOf = LEN([MyField]) - CHARINDEX(@indexOf, REVERSE([MyField]))

No ha probado, podría estar apagado por uno debido al índice cero, pero funciona en la función SUBSTRING al cortar de @indexOf caracteres al final de su cadena

SUBSTRING([MyField], 0, @LastIndexOf)

Necesitaba encontrar la enésima última posición de una barra invertida en una ruta de carpeta. Aquí está mi solución.

/*
http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql/30904809#30904809
DROP FUNCTION dbo.GetLastIndexOf
*/
CREATE FUNCTION dbo.GetLastIndexOf
(
  @expressionToFind         VARCHAR(MAX)
  ,@expressionToSearch      VARCHAR(8000)
  ,@Occurrence              INT =  1        -- Find the nth last 
)
RETURNS INT
AS
BEGIN

    SELECT  @expressionToSearch = REVERSE(@expressionToSearch)

    DECLARE @LastIndexOf        INT = 0
            ,@IndexOfPartial    INT = -1
            ,@OriginalLength    INT = LEN(@expressionToSearch)
            ,@Iteration         INT = 0

    WHILE (1 = 1)   -- Poor man's do-while
    BEGIN
        SELECT @IndexOfPartial  = CHARINDEX(@expressionToFind, @expressionToSearch)

        IF (@IndexOfPartial = 0) 
        BEGIN
            IF (@Iteration = 0) -- Need to compensate for dropping out early
            BEGIN
                SELECT @LastIndexOf = @OriginalLength  + 1
            END
            BREAK;
        END

        IF (@Occurrence > 0)
        BEGIN
            SELECT @expressionToSearch = SUBSTRING(@expressionToSearch, @IndexOfPartial + 1, LEN(@expressionToSearch) - @IndexOfPartial - 1)
        END

        SELECT  @LastIndexOf = @LastIndexOf + @IndexOfPartial
                ,@Occurrence = @Occurrence - 1
                ,@Iteration = @Iteration + 1

        IF (@Occurrence = 0) BREAK;
    END

    SELECT @LastIndexOf = @OriginalLength - @LastIndexOf + 1 -- Invert due to reverse
    RETURN @LastIndexOf 
END
GO

GRANT EXECUTE ON GetLastIndexOf TO public
GO

Aquí están mis casos de prueba que pasan

SELECT dbo.GetLastIndexOf('f','123456789\123456789\', 1) as indexOf -- expect 0 (no instances)
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 1) as indexOf -- expect 20
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 2) as indexOf -- expect 10
SELECT dbo.GetLastIndexOf('\','1234\6789\123456789\', 3) as indexOf -- expect 5

Para obtener la parte antes de la última aparición del delimitador (funciona solo para NVARCHAR debido al uso de DATALENGTH):

DECLARE @Fullstring NVARCHAR(30) = '12.345.67890.ABC';

DECLARE @Delimiter CHAR(1) = '.';

SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring)));

Algunas de las otras respuestas devuelven una cadena real mientras que yo tenía más necesidad de saber el índice real int. Y las respuestas que lo hacen parecen complicar demasiado las cosas. Usando algunas de las otras respuestas como inspiración, hice lo siguiente...

Primero, creé una función:

CREATE FUNCTION [dbo].[LastIndexOf] (@stringToFind varchar(max), @stringToSearch varchar(max))
RETURNS INT
AS
BEGIN
    RETURN (LEN(@stringToSearch) - CHARINDEX(@stringToFind,REVERSE(@stringToSearch))) + 1
END
GO

Entonces, en tu consulta puedes simplemente hacer esto:

declare @stringToSearch varchar(max) = 'SomeText: SomeMoreText: SomeLastText'

select dbo.LastIndexOf(':', @stringToSearch)

Lo anterior debe devolver 23 (el último índice de':')

Espero que esto lo haya hecho un poco más fácil para alguien!

Esta respuesta cumple con los requisitos del OP. específicamente permite que la aguja sea más de un solo carácter y no genera un error cuando la aguja no se encuentra en haystack. Me pareció que la mayoría(todos?) de las otras respuestas no se ocupaban de esos casos extremos. Más allá de eso, agregué el argumento "Posición inicial" proporcionado por la función nativa de MS SQL Server CharIndex. Traté de reflejar exactamente la especificación de CharIndex, excepto para procesar de derecha a izquierda en lugar de izquierda a la derecha. por ejemplo, devuelvo null si needle o haystack es null y devuelvo cero si needle no se encuentra en haystack. Una cosa que no pude evitar es que con la función incorporada el tercer parámetro es opcional. Con las funciones definidas por el usuario de SQL Server, todos los parámetros deben proporcionarse en la llamada a menos que se llame a la función usando "EXEC" . Si bien el tercer parámetro debe incluirse en la lista de parámetros, puede proporcionar la palabra clave "default" como marcador de posición para él sin tener que dale un valor (ver ejemplos a continuación). Dado que es más fácil eliminar el tercer parámetro de esta función si no se desea, de lo que sería agregarlo si es necesario, lo he incluido aquí como punto de partida.

create function dbo.lastCharIndex(
 @needle as varchar(max),
 @haystack as varchar(max),
 @offset as bigint=1
) returns bigint as begin
 declare @position as bigint
 if @needle is null or @haystack is null return null
 set @position=charindex(reverse(@needle),reverse(@haystack),@offset)
 if @position=0 return 0
 return (len(@haystack)-(@position+len(@needle)-1))+1
end
go

select dbo.lastCharIndex('xyz','SQL SERVER 2000 USES ANSI SQL',default) -- returns 0
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',default) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',1) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',11) -- returns 1

Me encontré con este hilo mientras buscaba una solución a mi problema similar que tenía exactamente el mismo requisito, pero era para un tipo diferente de base de datos que también carecía de la función REVERSE.

En mi caso, esto fue para una base de datos OpenEdge (Progress), que tiene una sintaxis ligeramente diferente. Esto hizo que la función INSTR estuviera disponible para mí que la mayoría de las bases de datos escritas por Oracle ofrecen .

Así que se me ocurrió lo siguiente código:

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/',  ''))) AS IndexOfLastSlash 
FROM foo

Sin embargo, para mi situación específica (siendo la base de datos OpenEdge (Progress)) esto no resultó en el comportamiento deseado porque reemplazar el carácter con un carácter vacío dio la misma longitud que la cadena original. Esto no tiene mucho sentido para mí, pero pude evitar el problema con el siguiente código:

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/',  'XX')) - LENGTH(foo.filepath))  AS IndexOfLastSlash 
FROM foo

Ahora entiendo que este código no resolverá el problema para T-SQL porque no hay alternativa a la INSTR función que ofrece la propiedad Occurence.

Solo para ser minucioso, agregaré el código necesario para crear esta función escalar para que pueda usarse de la misma manera que lo hice en los ejemplos anteriores.

  -- Drop the function if it already exists
  IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
    DROP FUNCTION INSTR
  GO

  -- User-defined function to implement Oracle INSTR in SQL Server
  CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
  RETURNS INT
  AS
  BEGIN
    DECLARE @found INT = @occurrence,
            @pos INT = @start;

    WHILE 1=1 
    BEGIN
        -- Find the next occurrence
        SET @pos = CHARINDEX(@substr, @str, @pos);

        -- Nothing found
        IF @pos IS NULL OR @pos = 0
            RETURN @pos;

        -- The required occurrence found
        IF @found = 1
            BREAK;

        -- Prepare to find another one occurrence
        SET @found = @found - 1;
        SET @pos = @pos + 1;
    END

    RETURN @pos;
  END
  GO

Para evitar lo obvio, cuando la función REVERSE está disponible, no necesita crear esta función escalar y puede obtener el resultado requerido de la siguiente manera:

SELECT
  LEN(foo.filepath) - CHARINDEX('/', REVERSE(foo.filepath))+1 AS LastIndexOfSlash 
FROM foo

Este código funciona incluso si la subcadena contiene más de 1 carácter.

DECLARE @FilePath VARCHAR(100) = 'My_sub_Super_sub_Long_sub_String_sub_With_sub_Long_sub_Words'
DECLARE @FindChar VARCHAR(5) = '_sub_'

-- Shows text before last slash
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(REVERSE(@FindChar), REVERSE(@FilePath)) - LEN(@FindChar) + 1) AS Before
-- Shows text after last slash
SELECT RIGHT(@FilePath, CHARINDEX(REVERSE(@FindChar), REVERSE(@FilePath)) -1) AS After
-- Shows the position of the last slash
SELECT LEN(@FilePath) - CHARINDEX(REVERSE(@FindChar), REVERSE(@FilePath)) AS LastOccuredAt