Encuentre el índice de última aparición de una subcadena usando T-SQL

¿Hay una manera directa de encontrar el índice de la última ocurrencia de una cadena usando SQL? Estoy usando SQL Server 2000 en este momento. Básicamente, necesito la funcionalidad que proporciona el método .NET System.String.LastIndexOf . Un poco de google reveló esto – Función para recuperar el último índice – pero eso no funciona si pasas una expresión de columna de “texto”. Otras soluciones que se encuentran en otros lugares funcionan solo mientras el texto que estás buscando tenga 1 carácter.

Probablemente tendré que cocinar una función. Si lo hago, lo publicaré aquí para que la gente pueda verlo y quizás hacer uso de él.

Está limitado a una pequeña lista de funciones para el tipo de datos de texto.

Todo lo que puedo sugerir es comenzar con PATINDEX , pero trabajar hacia atrás desde DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc. hasta que obtenga un resultado o termine en cero (DATALENGTH-DATALENGTH)

Esto realmente es algo que SQL Server 2000 simplemente no puede manejar.

Editar para otras respuestas : REVERSE no está en la lista de funciones que se pueden usar con datos de texto en SQL Server 2000

¿De manera directa? No, pero he usado el reverso. Literalmente.

En las rutinas anteriores, para encontrar la última ocurrencia de una cadena dada, utilicé la función REVERSE (), seguí CHARINDEX, y seguí otra vez con REVERSE para restablecer 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 de los archivos de la base de datos real de sus “nombres físicos”, sin importar qué tan nesteds estén en las 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 sé qué tan bien funcionará esto en los tipos de datos TEXT. He estado en SQL 2005 durante algunos años, y ya no estoy familiarizado con el trabajo con TEXT, pero me parece recordar que podría usar IZQUIERDA y DERECHA en él.

Felipe

La forma más simple 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 siguiente código 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]))) 

Una pregunta anterior pero válida, así que aquí está lo que creé en base a 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 recuento podría hacer esto en SQL2000 (o en 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 conteo es solo una tabla de incremento de números.

La 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 Tally son increíbles. Si no los ha usado antes, hay un buen artículo en SQL Server Central (Reg. Gratis, o simplemente use Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com )).

* EDITAR: eliminó n < = LEN(TEXT_FIELD) , ya que no puede usar LEN () en el tipo de texto. Mientras la substring(...) = @delim permanezca, el resultado sigue siendo correcto.

Invierta su cadena y su subcadena, luego busque la primera ocurrencia.

Me doy cuenta de que esta es una pregunta de 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 de text a varchar para que pueda usar la solución provista 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 de text desbordara la longitud de su varchar (sin mencionar que no sería muy eficiente).

Como sus datos están dentro de un campo de text (y está usando 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 (nombre, (CHARINDEX (”, REVERSE (nombre), 0)) para devolver la última palabra en la cadena. es útil si desea analizar el apellido de un nombre completo que incluya las iniciales para el primer y / o segundo nombre.

@indexOf =

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

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

SUBSTRING([MyField], 0, @LastIndexOf)

Necesitaba encontrar la enésima 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 pieza antes de la última ocurrencia 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 conocer el índice real int. Y las respuestas que hacen eso 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 

Luego, en su consulta, simplemente puede 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 sea un poco más fácil para alguien!

Esta respuesta cumple con los requisitos de OP. específicamente permite que la aguja sea más que un solo carácter y no genera un error cuando la aguja no se encuentra en el pajar. Me pareció que la mayoría (¿todas?) De las otras respuestas no manejaban esos casos extremos. Más allá de eso, agregué el argumento “Posición inicial” proporcionado por la función CharIndex del servidor MS SQL nativo. Traté de duplicar exactamente las especificaciones de CharIndex excepto para procesar de derecha a izquierda en lugar de izquierda a derecha. Por ejemplo, devuelvo nulo si la aguja o el pajar son nulos y devuelvo el cero si no se encuentra la aguja en el pajar. 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, se deben proporcionar todos los parámetros en la llamada a menos que se llame a la función utilizando “EXEC”. Si bien el tercer parámetro debe incluirse en la lista de parámetros, puede proporcionar la palabra clave “predeterminada” como marcador de posición sin tener que darle 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 es el deseado de lo que sería agregarlo si fuera 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 

Encontré este hilo mientras buscaba una solución para 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 syntax ligeramente diferente. Esto hizo que la función INSTR disponible para mí que ofrecen la mayoría de las bases de datos tipadas por Oracle .

Así que se me ocurrió el 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 (al ser la base de datos OpenEdge (Progress) ), esto no dio como resultado 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 eludir 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 función INSTR que ofrece la propiedad INSTR .

Para ser exhaustivo, agregaré el código necesario para crear esta función escalar, de modo que pueda usarse de la misma forma 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 esta 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